Pharsing string from the right to the first space

[Dean]

I would like to extract data from a cell based on everything after the first
space tothe left of the end of the data. ie Cell 1 contains "Mr. Dean L.
Davis", I would like cell 2 to extract everything from the last space to its
right. In this case "Davis".
I have looked but am missing it somewhere.

Thanks
Dean

 

[Gary Keramidas]

here's one way, assuming your data is on sheet1 starting in A1. adjust to your
needs

Option Explicit

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim rng As Range
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastrow
Set rng = ws.Range("A" & i)
ws.Range("B" & i).Value = Right(rng, Len(rng) - InStrRev(ws.Range("A" &
i).Value, " "))
Next
End Sub

 

[Ron Rosenfeld]

I would like to extract data from a cell based on everything after the first
space tothe left of the end of the data. ie Cell 1 contains "Mr. Dean L.
Davis", I would like cell 2 to extract everything from the last space to its
right. In this case "Davis".
I have looked but am missing it somewhere.

Thanks
Dean

As a worksheet formula:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)

Since you posted in a programming group, here is a UDF:

===========================
Option Explicit
Function LW(str As String) As String
Dim temp
temp = Split(str, " ")
LW = temp(UBound(temp))
End Function
=============================

One difference between the two is, as written, the worksheet function will
return an error message if there is only one word, whereas the UDF will return
that word.

Not sure what you want to do if there is only a single word, but these
behaviors are easily modified, depending on your requirements.
--ron

 

[Mike H]

try this


=MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,256)

Mike

 

[Rick Rothstein \(MVP - VB\)]

As a worksheet formula:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)

One difference between the two is, as written, the worksheet function will
return an error message if there is only one word, whereas the UDF will
return
that word.

Not sure what you want to do if there is only a single word, but these
behaviors are easily modified, depending on your requirements.

You can make the worksheet formula do the same by adding a blank space in
front of the A1 references...

=MID(" "&A1,1+FIND(CHAR(1),SUBSTITUTE(" "&A1," ",
CHAR(1),LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," ","")))),255)

I'm pretty sure your last paragraph indicates you know this, but I figured I
would post the message for those reading this thread in the archives.

Rick

 

[Ron Rosenfeld]

You can make the worksheet formula do the same by adding a blank space in
front of the A1 references...

=MID(" "&A1,1+FIND(CHAR(1),SUBSTITUTE(" "&A1," ",
CHAR(1),LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," ","")))),255)

I'm pretty sure your last paragraph indicates you know this, but I figured I
would post the message for those reading this thread in the archives.

Rick

Yeah, I know. Too many options without enough guidance.

By the way, you can have the worksheet formula return a blank by appending a
<space> to the reference:

=MID(A1&" ",1+FIND(CHAR(1),SUBSTITUTE(A1&" "," ",CHAR(1),
LEN(A1&" ")-LEN(SUBSTITUTE(A1&" "," ","")))),255)

And, of course, the UDF can be easily modified to do any of those options.
--ron