Forum Discussion
Extract data after - in power bi
Hello All,
I have table having data like below.
USA-West
USA-East
I need to extract text after -
Please suggest how to do
You'd have to use IF() and then add a search for something that is unique for those rows. I assumed "(" is unique for these and did below, but you might have to adjust accordingly:
Column = IF( SEARCH("(",'Table'[Name],1,-1) > 0, MID( 'Table'[Name], SEARCH("(",'Table'[Name]) + 1, LEN('Table'[Name]) - SEARCH("(",'Table'[Name]) - 1 ), MID( 'Table'[Name], SEARCH("-",'Table'[Name],1,0) + 1, LEN('Table'[Name]) - SEARCH("-",'Table'[Name],1,0) ) )Hope this helps!
Did my answer help? Feel free to give kudos and mark as solution to show your support. Thanks!
6 Replies
- TomasAndersson
Solution Sage
Hi!
You can use a combination of SEARCH(), MID() and LEN().Column = MID( 'Table'[Name], SEARCH("-",'Table'[Name]) + 1, LEN('Table'[Name]) - SEARCH("-",'Table'[Name]) )
Hope this helps!- TomasAndersson
Not seing the specific error but could be that you have some rows that do not have "-" which causes SEARCH() to throw an error. You can adjust the calculated column to this then:
Column = MID( 'Table'[Name], SEARCH("-",'Table'[Name],1,0) + 1, LEN('Table'[Name]) - SEARCH("-",'Table'[Name],1,0) )
- themistoklis
Community Champion
Anonymous
If there are names without a dash then you can add a handling error function
Column = IFERROR(MID(Sheet1[Name], FIND("-",Sheet1[Name])+1,300), Sheet1[Name])
