Thread: Extract the last word from the cell

Dear all,

Please help me to extract the last word from the cells of a certain column.

File attached

Senjuti

Here, try this:

=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",255)),150))

Hi,

try:

=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",100)),50))

Regards

Edit: Sorry Zbor: same answer. Regards

How do you mean? There is no space in resulting cell.

That's because you don't have spaces but some other characters.

Here, this should work for you:

=TRIM(RIGHT(SUBSTITUTE(B2,CHAR(160),REPT(" ",255)),150))


Edit: but that won't work for other data...

In that case first copy one character from your result that is not trimed.
Select B column and replace (ctrl+H) that character with space.

Then it will work.

Other option with one formula is this:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(B2,CHAR(160)," ")," ",REPT(" ",255)),150))