Forum Discussion
Helper IIExtracting data from a column after a certain character
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?
You can just add a new column with this formula
= Text.BetweenDelimiters([Col Name], "-", "-")
Regards,
Pat
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.
2 Replies
- mahoneypat
Microsoft Employee
You can just add a new column with this formula
= Text.BetweenDelimiters([Col Name], "-", "-")
Regards,
Pat
- PhilipTreacy
Super User
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.