pulling apart a field

  • Thread starter Thread starter TheObstacleIsThePath
  • Start date Start date
T

TheObstacleIsThePath

sample field:
HYDROXYZINE PAM TYA** 2 DOE, JOHN

sample output:
DOE, JOHN

I would like to pull out the name from the field above. Cutting point
should be at the nearest space preceding the last comma.

I'm having trouble wrapping my brain around it. I'd prefer to do it
with functions rather than VB script, but will be glad with either
solution.

Your help is very appreciated.

--T
 
Can you post some more of your data? It's hard to figure out the best
method for the job without more samples.


Thx,
JP
 
How about a user defined function (UDF)? Just copy the code below and insert
it into a regular code module: open the VB Editor using [Alt]+[F11], then
choose Insert | Module from its menu and copy and past the code below into it
--

Public Function GetWholeName(cellText As String) As String
Dim workingText As String
Dim firstNameOnly As String

On Error GoTo 0
firstNameOnly = Right(cellText, Len(cellText) - _
InStrRev(cellText, ","))
workingText = Left(cellText, _
Len(cellText) - Len(firstNameOnly))
GetWholeName = Right(workingText, Len(workingText) - _
InStrRev(workingText, " ")) & firstNameOnly
If Err <> 0 Then
GetWholeName = ""
Err.Clear
End If
On Error GoTo 0
End Function


To use the function on a worksheet, use formula like this:
=getwholename(A1)
that assumes that your sample text is in cell A1. Change A1 in the formula
to the cell(s) that contain the text to be parsed.


If, for some reason you need to separate out just the last name, then this
UDF will do that for you:

Public Function GetLastName(cellText As String) As String
On Error Resume Next
GetLastName = Right(Left(cellText, InStrRev(cellText, ",") - 1), _
Len(Left(cellText, InStrRev(cellText, ",") - 1)) - _
InStrRev(Left(cellText, InStrRev(cellText, ",") - 1), " "))
If Err <> 0 Then
GetLastName = ""
Err.Clear
End If
 
Let's assume your string is in A1. For simplicity, I would suggest
using two formulas. One is to find the position of the comma:

B1: =LEN(A1)-MATCH(",",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1),
0)

Use Ctrl+Shift+Enter. Result for sample input is 28, which is the E
of DOE.

Next, to find the position of the space immediately preceding it (also
Ctrl+Shift+Enter):

C1: =MATCH(CHAR(222),MID(SUBSTITUTE(A1," ",CHAR(222),LEN(LEFT(A1,B1))-
LEN(SUBSTITUTE(LEFT(A1,B1)," ",""))),ROW(INDIRECT("1:"&LEN(A1))),1),0)

Finally, extract using RIGHT():

D1: =RIGHT(A1,LEN(A1)-C1)

It's kind of a brute-force solution, but it works. Calculation would
take about 0.6 seconds for 5000 records.

By the way, a single-cell way of doing exact same algorithm looks like
the following, but will only work in Excel 2007 due to function
nesting limitations of previous versions:

=RIGHT(A1,LEN(A1)-MATCH(CHAR(222),MID(SUBSTITUTE(A1,"
",CHAR(222),LEN(LEFT(A1,LEN(A1)-MATCH(",",MID(A1,LEN(A1)+1-
ROW(INDIRECT("1:"&LEN(A1))),1),0)))-LEN(SUBSTITUTE(LEFT(A1,LEN(A1)-
MATCH(",",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1),0)),"
",""))),ROW(INDIRECT("1:"&LEN(A1))),1),0))

Hope that helps.
 
I'd go for a stepwise solution. It can probably done in one formula (I can't; too much thinking required!)

Assume text is in A1.

In B1:
LEN(A1)-LEN(SUBSTITUTE(A1,",","")) determines the number of commas

In B2:
=SUBSTITUTE(A1,",","~",B1) replaces the last comma with a tilde

In B3:
=FIND("~",B2) finds the position of the tilde (last comma)

In B4:
=LEFT(A1,B3-1) the part to the left of the last comma

In B5:
=LEN(B4)-LEN(SUBSTITUTE(B4," ","")) the number of spaces in the left part

In B6:
=SUBSTITUTE(A1," ","~",B5) replaces the last space in that part with a tilde

In B7:
=FIND("~",B6) finds the position of the tilde (tea last space in the left part)

In B8:
=RIGHT(A1,LEN(A1)-B7) from the original text, the part to the right of the last space before the last comma


--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| sample field:
| HYDROXYZINE PAM TYA** 2 DOE, JOHN
|
| sample output:
| DOE, JOHN
|
| I would like to pull out the name from the field above. Cutting point
| should be at the nearest space preceding the last comma.
|
| I'm having trouble wrapping my brain around it. I'd prefer to do it
| with functions rather than VB script, but will be glad with either
| solution.
|
| Your help is very appreciated.
|
| --T
 
This works for your posted example bit I think on balance I prefer the UDF <G>

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


Mike
 
Here is a one-liner UDF that does what the OP asked...

Function GetWholeName(Text As String) As String
GetWholeName = Mid(Text, InStrRev(Text, " ", InStrRev(Text, ",")) + 1)
End Function

It returns a #VALUE! error if the cell's text doesn't have a comma in it.
Your UDF returns the entire cell's text for that condition. Personally, I
might favor returning the empty string if a comma is not present; this UDF
will do that...

Function GetWholeName(Text As String) As String
If InStr(Text, ",") Then GetWholeName = _
Mid(Text, InStrRev(Text, " ", InStrRev(Text, ",")) + 1)
End Function

Rick


JLatham said:
How about a user defined function (UDF)? Just copy the code below and
insert
it into a regular code module: open the VB Editor using [Alt]+[F11], then
choose Insert | Module from its menu and copy and past the code below into
it
--

Public Function GetWholeName(cellText As String) As String
Dim workingText As String
Dim firstNameOnly As String

On Error GoTo 0
firstNameOnly = Right(cellText, Len(cellText) - _
InStrRev(cellText, ","))
workingText = Left(cellText, _
Len(cellText) - Len(firstNameOnly))
GetWholeName = Right(workingText, Len(workingText) - _
InStrRev(workingText, " ")) & firstNameOnly
If Err <> 0 Then
GetWholeName = ""
Err.Clear
End If
On Error GoTo 0
End Function


To use the function on a worksheet, use formula like this:
=getwholename(A1)
that assumes that your sample text is in cell A1. Change A1 in the
formula
to the cell(s) that contain the text to be parsed.


If, for some reason you need to separate out just the last name, then this
UDF will do that for you:

Public Function GetLastName(cellText As String) As String
On Error Resume Next
GetLastName = Right(Left(cellText, InStrRev(cellText, ",") - 1), _
Len(Left(cellText, InStrRev(cellText, ",") - 1)) - _
InStrRev(Left(cellText, InStrRev(cellText, ",") - 1), " "))
If Err <> 0 Then
GetLastName = ""
Err.Clear
End If
sample field:
HYDROXYZINE PAM TYA** 2 DOE, JOHN

sample output:
DOE, JOHN

I would like to pull out the name from the field above. Cutting point
should be at the nearest space preceding the last comma.

I'm having trouble wrapping my brain around it. I'd prefer to do it
with functions rather than VB script, but will be glad with either
solution.

Your help is very appreciated.

--T
Click to expand...
Click to expand...
 
Rick,
I'm embarrassed - I meant to return an empty string, not the entire original
text. I agree, more desirable although either one would indicate a failure
to function.

Thanks for turning it into the one-liner. I'd worked out the one for last
name only, but got a bit rushed (at the end of lunch hour) to do the full
name as a one-liner.

I'm waiting so read the rest of the responses to see if this can be done
easily via worksheet functions.

JLatham said:
How about a user defined function (UDF)? Just copy the code below and insert
it into a regular code module: open the VB Editor using [Alt]+[F11], then
choose Insert | Module from its menu and copy and past the code below into it
--

Public Function GetWholeName(cellText As String) As String
Dim workingText As String
Dim firstNameOnly As String

On Error GoTo 0
firstNameOnly = Right(cellText, Len(cellText) - _
InStrRev(cellText, ","))
workingText = Left(cellText, _
Len(cellText) - Len(firstNameOnly))
GetWholeName = Right(workingText, Len(workingText) - _
InStrRev(workingText, " ")) & firstNameOnly
If Err <> 0 Then
GetWholeName = ""
Err.Clear
End If
On Error GoTo 0
End Function


To use the function on a worksheet, use formula like this:
=getwholename(A1)
that assumes that your sample text is in cell A1. Change A1 in the formula
to the cell(s) that contain the text to be parsed.


If, for some reason you need to separate out just the last name, then this
UDF will do that for you:

Public Function GetLastName(cellText As String) As String
On Error Resume Next
GetLastName = Right(Left(cellText, InStrRev(cellText, ",") - 1), _
Len(Left(cellText, InStrRev(cellText, ",") - 1)) - _
InStrRev(Left(cellText, InStrRev(cellText, ",") - 1), " "))
If Err <> 0 Then
GetLastName = ""
Err.Clear
End If
sample field:
HYDROXYZINE PAM TYA** 2 DOE, JOHN

sample output:
DOE, JOHN

I would like to pull out the name from the field above. Cutting point
should be at the nearest space preceding the last comma.

I'm having trouble wrapping my brain around it. I'd prefer to do it
with functions rather than VB script, but will be glad with either
solution.

Your help is very appreciated.

--T
Click to expand...
Click to expand...
 
Thanks to all. The step-at-a-time methods are most useful because I
can understand them. If crammed into a single formula, I get lost.
 
Back
Top