Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have table having data like below.
USA-West
USA-East
I need to extract text after -
Please suggest how to do
Solved! Go to Solution.
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!
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])
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!
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)
)
Hi @TomasAndersson ,
Thanks for your answer.
I need to check one more condition here.
If my column having values like below and I need to show like this
if Column = 'SupportL2 – (ESI-India)' then ESI-India
if Column = 'SupportL2 – APAC (ESI-China)' then ESI-China
if Column = 'SupportL2 – APAC (ESI-PCS)' then ESI-PCS
and if the column having values apart from this I need use the logic we created above.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |