Remove last word in text string

[RifDarr]

Hi,

How do I remove the last word in each cell from a list, each with varying length? (ie, some have 7 words others have 5 etc)

Thanks

 

[barry houdini]

If your data is in A1 this formula in B1 will return all but the last word

=LEFT(A1,LOOKUP(2^15,FIND(" "," "&A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)

 

[RifDarr]

Thanks, that's brilliant!

 

[MickG]

Hi, Just an Alternative !

Sub Lword()
Dim rng As Range, Dn As Range
Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    For Each Dn In rng
        If InStr(Dn, " ") Then
            Dn = Left(Dn, InStrRev(Dn, " ") - 1)
        End If
    Next Dn
End Sub

Regards Mick