Return Nth word from the RIGHT

[Keti89]

Hello Everyone,

After a lot of thinking and reading I could not find a solution, so I thought someone might be able to help.

I managed to return first and last word from a cell, even managed to return the Nth word from a text, but could not manage to find a function to return Nth word, if looking from the right to the left within a cell.

Basically, I need this, because the position within which the value I need is found is only FIX from the right (as in the middle of the strings there is a variable character count, so the length is different in every row)

Get first word
=LEFT(B2,FIND(" ",B2)-1)

Get last word
=TRIM(RIGHT(SUBSTITUTE(TRIM(B6)," ",REPT(" ",100)),100))

Extract Nth word from string
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1)))

(Credit goes to Dave Bruns' Exceljet for most of the above.)

All the Best,

Tibi

 

[footoo]

What about these???

What about them?
Based on the original post, why would you think the OP's data is something other than words separated by space(s) ?
If that's the case, he will surely post again.

 

[jtakw]

What about them?
Based on the original post, why would you think the OP's data is something other than words separated by space(s) ?
If that's the case, he will surely post again.

Because I've seen many. Don't want to HiJack this post on a trivial matter, I'm Out.

 

[footoo]

What about these???

Let's not guess what OP is asking, OP needs to specify the question as I asked in post #3.

A guess?
Don't you mean an extremely likely understanding of the OP's requirement

 

[Keti89]

Thanks Everyone for the massive input, much appreciated!

Yes, you can assume all the words are separated by a SPACE, as I think that is the case in every scenario! Although, if there is more than 1 space, I could perhaps substitute all the space strings with 1 space (the separator is always a space, so no other characters, in this case at least).

The problem with a simple example.

Finding Nth from the left sometimes will not work, because in the middle of the string there are sections where the number of words vary, so in these cases I would need to count from the right.

Example:

1.0 47% Ask for Help 10 Excel
2.0 47% Thank you for the help 20 Excel
3.0 47% It is always very nice to discuss 30 Excel

I would want to extract 10,20 and 30, so the second word from the right.

Al the Best,

Tibi

 

[wideboydixon]

Did you look at Post #4?

 

[Keti89]

Yes! Was just checking the details on that and it seems to be working correctly, so thanks a lot, Wideboydixon!

Also, I just wanted to reflect on the fact that there is no clear cut solution, the ones I brought in were examples, assuming the methodology will be similar.
But hey, this space for creativity is what makes this fun.

 

[Keti89]

Actually, both this one and the one in post #2 by footoo work, so thanks for the inputs and great solutions. This really shows that even a very complex issue can have multiple, creative solutions.