Extracting data from a column after a certain character

[Mal_Sondh]

Hi,

What is the best way to extract data such as this into another column:

If i have an original field called Col name and i wanted to normailse this as shown in the Normalised column, what formula would i need to write?

In basic format, the logic would be as follows:

Read the characters of the Col Name until you get to the first '-',  Ignore all chars before the '-', if another '-' exists take all chars between this and the orginal '-' and store in the new column(Normailsed Name), otherwise just take the rest of the chars and store in the new column (Normailsed Name) - see expected output below:

Col Name		Normalised Name
abc - abc123		abc123
xyz - xyz123 - 456		xyz123

Any ideas?

[mahoneypat · Accepted solution]

You can just add a new column with this formula

= Text.BetweenDelimiters([Col Name], "-", "-")

Regards,

Pat

[PhilipTreacy · Accepted solution]

Hi @Mal_Sondh 

Further to that, wrap it in Text.Trim to remove leading and trailing space characters

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Trim(Text.BetweenDelimiters([Col Name], "-", "-")))

Phil 

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[mahoneypat · Accepted solution]

You can just add a new column with this formula

= Text.BetweenDelimiters([Col Name], "-", "-")

Regards,

Pat