Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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 On Feb 12, 3:19*pm, " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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 On Error GoTo 0 End Function " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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. On Feb 12, 3:19 pm, " wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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 wrote in message ... | 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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 " wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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 On Error GoTo 0 End Function " wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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" wrote: 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 On Error GoTo 0 End Function " wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling apart a field
Hello,
My suggestion would have been =regexpreplace(A1,"^.* ([^,]*,.*)$","$1") In case of an error it would return the whole text, too. The UDF is he http://www.sulprobil.com/html/regexp.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula, one field complex or multiple table field simple | Excel Discussion (Misc queries) | |||
How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
Linked date field in worksheet defaults a blank field as 1/0/1900 | Excel Worksheet Functions | |||
How to Join/concatenate a date field with a time field in Excel? | Excel Discussion (Misc queries) | |||
Q: assign field background color dynamically according to field values | Excel Programming |