How do extract text after last dash? (Mid formula??)

[bsafra1]

Hi, I have a long list of values that look like “US - CA - Los Angeles” or “France - Paris”. What formula can I use to isolate the city (everything after the last dash and space?)

[bluesky63]

Hi, there are various methods do it depending on your volume of datasets, if it is a one-off extraction then

=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",LEN(A2))),LEN(A2)))


If you have massive amount of records need to transform, and also constantly new data flow in for updating, then Power Query (just build the query once and updating just right-click and refresh) is the more efficiency method ( see my simple query in Power Query Editor)


Note:- you can download the add-On of Power Query to your Excel 201