Return Nth word from the RIGHT

Keti89

New Member
Joined
Feb 8, 2018
Messages
10
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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
AB
1Those were the best days of my life
2
3N:2
4Nth word:were
5Nth word from the right:my
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0
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
 
Last edited:
Upvote 0
Then I think you need to state your problem more concretely. Some examples of what you're processing and the expected output.

WBD
 
Upvote 0
Don't ask me, ask OP, that's why I said what I said in post #3
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top