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
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