Extracting the first letter after a defined text string within a column

[MichaelBauld]

Hi all, 

I have a dashboard showing invoice payments by supplier name. This is filtered by the first letter of each supplier account as processing invoices is assigned to staff  by the first letter of the supplier name (i.e. staff member 1 has invoices from suppliers starting with 'E' and 'F'). To pull the first letter I initially just used a calculated column alongside the 'supplier name' column to pull the first letter from the left of the supplier name:

=LEFT('Pay run data'[Supplier name],1))

However, it appears that some supplier names have a trading name and an invoicing name. These supplier accounts usually list the name in the following format:

EXAMPLE: 'John Scott Joinery t/a Scott Joinery' (so should be logged a 'S' not 'J')

So my challenge is, for suppliers where this is relevant, how can I pull the first letter after 't/a' in the name (or the first letter of the whole text string if 't/a' is not present)? Any advice would be gratefully recieved!

Thanks

Michael

[ddpl]

@MichaelBauld ,

Try below steps in Power Query...

Step 1: Replace "t/a " (t/a[space]) with ","

Step 2: Add a Custom Column...

Your Need = if Text.Contains( [Suppliers], "," ) = true 
            then Text.Start( Text.AfterDelimiter( [Suppliers], "," ), 1 ) 
            else Text.Start( [Suppliers], 1 )

Hope it will work for you.

[amitchandak · Accepted solution]

@MichaelBauld , Try like

Pos = var _pos = SEARCH([Column1],,0)+4
return
mid([Column1],_pos,1)

[MichaelBauld]

Thanks @amitchandak , using this variable and combined with the IF and MID functions I've the column pulling the correct letters.