pick up the word from right end

[reedzhou]

Hi which formular to use to pick up the last word from right end?

601 St Kilda Road Suncorp/ AAMI Level GD Melbourne
Inala Avenue Inala Plaza Shopping Centre Shop C7(B)&C8, Inala,
Coles Liquor Group Liquorland North Lakes 1 Macmillans Road North Lakes

[Marcol]

Try this

Please Login or Register  to view this content.

N.B.
255 is perhaps a bit excessive, but it should work for all circumstances.

[reedzhou]

Hi not working, I only pick Melbourne/Inala/lakes.

[Marcol]

That is because "Melbourne/Inala/lakes" is all one word i.e. no spaces, as your first post suggested words are conventionally separated by spaces.

Perhaps you should post a sample of the type of data you require the last word to be extracted from so that we can identify what separators we need to consider.

[reedzhou]

Thanks for your kind reply.

Please see the attached, thanks

[Marcol]

1/. So where did you get "Melbourne/Inala/lakes" from?

2/. How do we decide whether to return the last word, as your post suggests, or the last two words, as your sample asks for in the case of "North Lakes"?
There are no consistant separators to allow us to do this.

3/. All of your sample strings have trailing spaces in varying amounts. How does that happen?
It makes string comparism unreliable.
You should at least use TRIM() to clean the list before going further.

4/. This modification in C6 will return the last word from your sample data

Please Login or Register  to view this content.

or if you have a trailing comma in the result, such as in "Inala, ", this will remove it.

Please Login or Register  to view this content.

As for "North Lakes" this might do it to some extent, but it is not at all reliable, and I'm sure there will be other cases when you want to return more than the last word.

Please Login or Register  to view this content.

If you have a known list of second last words we could maybe use an array formula to go part of the way to solving this.
Put your known second last words in a separate list say F6:F9 (no blanks)
Then in C2, this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter.
Then Drag/Fill Down.

This is not bullet proof but it might go some way to helping.