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]

With the Nth number in A2 :

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),60*A2),60))

(Above formula obtained from : https://www.mrexcel.com/forum/excel-questions/331234-extract-2nd-last-word.html)

 

[jtakw]

Hi,

So, what's your question?

FYI, there are no single magical formula for finding "first word", "last word", or Nth word.
If you care to share what you're trying to solve, maybe someone can help.

 

[wideboydixon]

I think there's just a last step to work out how many words are in the string and feed that into the last formula you have:

Book1
	A	B
1	Those were the best days of my life
2
3	N:	2
4	Nth word:	were
5	Nth word from the right:	my
Sheet1

Cell Formulas
Range	Formula
B4	=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), ($B$3-1)*LEN(A1)+1, LEN(A1)))
B5	=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1-$B$3)*LEN(A1)+1, LEN(A1)))

WBD

 

[jtakw]

Those formulae relies on the the WORDS are separated by a SPACE " ".

Won't work if: 1WORD2secondword3THIRDWORDetc4words

or: 1 2 3 word 4 word 5 another word 6 word 7 8 9

 

[wideboydixon]

Then I think you need to state your problem more concretely. Some examples of what you're processing and the expected output.

WBD

 

[jtakw]

Don't ask me, ask OP, that's why I said what I said in post #3

 

[wideboydixon]

Don't ask me, ask OP, that's why I said what I said in post #3

Ah. Oops. Sorry; lost track of the OP there :D

WBD

 

[footoo]

Those formulae relies on the the WORDS are separated by a SPACE " ".

Based on the formulas in the OP's post, that would appear to be what the OP is looking for.
The formulas also work if the words are separated by more than one space

 

[jtakw]

Those formulae relies on the the WORDS are separated by a SPACE " ".

Won't work if: 1WORD2secondword3THIRDWORDetc4words

or: 1 2 3 word 4 word 5 another word 6 word 7 8 9

What about these???

Let's not guess what OP is asking, OP needs to specify the question as I asked in post #3 , not saying I can solve it once OP answers, but it would definitely narrow down the possibilities.