Extract everything to the Right of the Right-most space in a String

[DougRobertson]

Hello,

In the String "John Paul Jones" I'm looking for the Excel formula that would extract "Jones" - everything to the Right of the Right-most space.

Many thanks in advance,

~ DWR

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TAD = Table.AddColumn(Source, "TAD", each Text.AfterDelimiter([raw], " ", {0, RelativePosition.FromEnd}), type text)
in
    TAD

raw	TAD
John Paul Jones	Jones

 

[Rick Rothstein]

Give this formula a try...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

where the formula assumes your text will be less than 99 characters long (if they could be longer, increase the 99's to a value guaranteed to be larger than your largest text string).

 

[mohadin]

And VBA Option
You text In A1 down

Sub test()
    Dim i As Long
    Dim a As Variant
    a = Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 1 To UBound(a)
        a(i, 1) = Split(a(i, 1))(UBound(Split(a(i, 1))))
    Next
    Cells(1, 1).Offset(, 1).Resize(UBound(a)) = a
End Sub

 

[Peter_SSs]

Another vba option is to do all rows at once.
Assuming data in column A and you want results in column B

Sub ExtractRight()
  With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -1).Value
    .Replace What:="* ", Replacement:="", LookAt:=xlPart
  End With
End Sub

 

[DougRobertson]

Thanks for all the help on this everyone! Rick's offering was more what I was looking for however.

Again, many thanks,

~ DWR

 

[Peter_SSs]

Cheers. Glad you got a successful outcome.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[DougRobertson]

Thanks Peter - good suggestion!

 

[Peter_SSs]

Thanks Peter - good suggestion!

No problem. Thanks for updating your profile.