Power Query - Extract strings only after the last delimiter

[uckycool]

Hi!
I want to extract strings from a list using Power Query.
This is my list

list
www.commerce.com/apple
www.commerce.com/bus
www.commerce.com/men/camp
www.commerce.com/women/disco
www.commerce.com/women/outfit/east
www.commerce.com/women/outfit/dress/flamingo

and I want to extract only the last string after the last slash "/', making the result like this:

list
apple
bus
camp
disco
east
flamingo

Hope anyone can help. Thanks!

[azumi]

Try:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1),"/",REPT(" ",255)),255))

[alansidman]

Since you are using Power Query, please update your profile which is still showing XL2007 for which PQ is not available.

Please Login or Register  to view this content.

[uckycool]

OK, my profile's changed now. thanks for letting me know alansidman.

but turns out I'm giving my example wrong. some instances have another "/" in the back as well so extract after delimiter is not solving these instances.

the original list should be like this (added "/" on the back of the strings for rows 3, 4, 5):

list
www.commerce.com/apple
www.commerce.com/bus
www.commerce.com/men/camp/
www.commerce.com/women/disco/
www.commerce.com/women/outfit/east/
www.commerce.com/women/outfit/dress/flamingo

with the expected result same like this:

list
apple
bus
camp
disco
east
flamingo

hope you also have answer to this? thank you.

[AliGW]

Uh-oh - shifting goalpost alert!!!

1. Must this be a PQ solution, or would a formula do?
2. Are there any other possibilities that you haven't included here, or is this the full extent of the issue? Are the items to be extracted always going to be single words, for example?

[uckycool]

Hi!
1. yes must be in PQ
2. yes, this is already the full extent of the issue. there might be a case where two words but joined by a dash "-", but the string shall be only surrounded by slash "/"

[AliGW]

M Code:

Please Login or Register  to view this content.

[XLent]

you could use extract between, as opposed to splits etc, e.g.:

Please Login or Register  to view this content.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Czeslaw]

My version

Please Login or Register  to view this content.