Help with formula

[adoalan]

Hi All,

I need some help in removing -UK from both columns Source Code and Allocation

and also everything was is after / in the Source Code

Any help please?

Thanks

[DOLEARY85]

Hi,

you can do this in power query, just right click the columns, select replace values type -uk in the first and leave the second blank

[adoalan]

@DOLEARY85  I can't, wont let me if I'm querrieng the data from the database. To use Power Query I need to import the data and will take ages.

[DOLEARY85]

Ah okay, 

for the -uk part you could create a column in report view:

Column = SUBSTITUTE('Table (2)'[Column1],"-uk","")

[DOLEARY85]

For the part after the /  you could use the search function to count the starting character using wildcards in the search e.g 

SEARCH("???-uk",'Table (2)'[Source Code], 1, 0)

then use a Left function to get the left value of the string up to 1 character before the start:

full column: 

left('Table (2)'[Source Code],SEARCH("???-uk",'Table (2)'[Source Code], 1, 0)-1 )