extract string from right till the space 4

[Bilberry]

Hi all,
Does anybody know how to extract a text till a space from the right?
For examle

1234 DF DDDDD

I want to extract only DDDDD

 

[xlhelp]

If your data is in cell A2, then

=TRIM(SUBSTITUTE(A2,TRIM(LEFT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),198)),""))

should extract any text after the second space

To find last word in any string (I have tried only to 5 words)

=IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))




Avoid Hangovers. Stay drunk.

 

[Bilberry]

Thannks a lot xlhelp. Im still busy with your formulas:

But what if i only have one word? We can have such values:

1234 DF DDDDD
DDFFKD
1123 JJ KKKKK

 

[combo]

In case of error (no space inside) use whole text instead of space:
=IF(ISERR(FIND(" ",A2)),A2,RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

combo

 

[Andrzejek]

Instead of a formula, would a little macro be a possibility?

If staring in A2 you have:[tt]
1234 DF DDDDD
DDFFKD
1123 JJ KKKKK[/tt]

This little macro:

Dim r As Integer
Dim s() As String

r = 2
Do While Range("A" & r).Value <> ""
    s = Split(Range("A" & r).Value, " ")
    Range("B" & r).Value = s(UBound(s))
    r = r + 1
Loop

puts this starting in B2:[tt]
DDDDD
DDFFKD
KKKKK
[/tt]
This macro will stop at the first empty cell in column A

Have fun.

---- Andy

 

[SkipVought]

or a user define function that you can use on your sheet...

Function LastWord(s As String) As String
    dim a
    a = Split(s, " ")
    LastWord = a(UBound(a))
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[byundt]

As long as the last word contains fewer than 99 characters, you can return it with:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

This formula will work even if the cell contains a single word (i.e. no spaces).

 

[byundt]

If the cell text ends with one or more spaces, my formula will not give the correct answer. The remedy is another TRIM:
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

That formula will also work if the cell is blank, contains an empty string, or contains the Gettysburg Address.