Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text Separation Function
I have a database with one column of information including names (First Last,
or First Middle Last) and for some names, locations in parenthesis. I am looking for a function that will allow me to split these pieces into separate columns for First Name, Middle Name (where applicable), Last Name, and Location (where listed). Does this exist? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text Separation Function
On Fri, 15 May 2009 16:44:02 -0700, kateconrey
wrote: I have a database with one column of information including names (First Last, or First Middle Last) and for some names, locations in parenthesis. I am looking for a function that will allow me to split these pieces into separate columns for First Name, Middle Name (where applicable), Last Name, and Location (where listed). Does this exist? It may be doable, depending on the patterns. You can search the string for the patterns which define the various substrings you wish to extract. You'll have to give some examples that include all the variations of what you want to do. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text Separation Function
Hi Kate,
Assuming your data looks like this: John Smith John Eric Smith John Smith (Seattle) John Eric Smith (Boston) I would set up some intermediary columns: (I'm starting at row 2, assumeing headers in row 1) Column B: Word Count =itCOUNTINCELL(A2," ")+1 Column C: Has Location =IF(RIGHT(A2,1)=")",TRUE,FALSE) Column D: Has Middle =IF(OR(AND(B2=3,NOT(C2)),B2=4),TRUE,FALSE) Column E: Last Word =IF(OR(AND(B2=2,NOT(C2)),AND(B2=3,C2)),2,3) Column F: Loc Word =IF(C2,B2,FALSE) Then use those to split out the First, Middle, Last, and Location: Column H: First Name =itGETWORD(A2,1) Column I: Middle Name =IF(D2,itGETWORD(A2,2),"") Column J: Last Name =itGETWORD(A2,E2) Column K: Location =itEXCLUDE(itGETWORD(A2,F2,,,," "),"(",")") To get itCOUNTINCELL, itGETWORD, and itEXCLUDE, you'll need to download and install the Free Edition of inspector text: (it never expires) http://precisioncalc.com/it For more information on each of those three functions: itCOUNTINCELL http://precisioncalc.com/it/itCOUNTINCELL.html itGETWORD http://precisioncalc.com/it/itGETWORD.html itEXCLUDE http://precisioncalc.com/it/itEXCLUDE.html Good luck with your project! Greg Lovern http://PrecisionCalc.com More Power In Excel On May 15, 4:44*pm, kateconrey wrote: I have a database with one column of information including names (First Last, or First Middle Last) and for some names, locations in parenthesis. *I am looking for a function that will allow me to split these pieces into separate columns for First Name, Middle Name (where applicable), Last Name, and Location (where listed). *Does this exist? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text Separation Function
On Fri, 15 May 2009 21:21:01 -0400, Ron Rosenfeld
wrote: On Fri, 15 May 2009 16:44:02 -0700, kateconrey wrote: I have a database with one column of information including names (First Last, or First Middle Last) and for some names, locations in parenthesis. I am looking for a function that will allow me to split these pieces into separate columns for First Name, Middle Name (where applicable), Last Name, and Location (where listed). Does this exist? It may be doable, depending on the patterns. You can search the string for the patterns which define the various substrings you wish to extract. You'll have to give some examples that include all the variations of what you want to do. --ron Looking at Greg's examples: First Last First Middle Last First Last (Location) First Middle Last (Location) IF that is correct, here is another approach with a User Defined Function. To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this User Defined Function (UDF), enter these formulas for the particular portions, assuming that your data is in A2. They should return a blank for Middle or Location if not present. First Name: =RegexMid(A2,"(\w+)",1) Middle Name: =RegexMid(A2,"\w+\s+(\w+)\s+(?!\()",1) Last Name: =RegexMid(A2,"(\w+$|\w+(?=\s+\())",1) Location: =RegexMid(A3,"\(([^)]+)",1) ================================================= Option Explicit Function RegexMid(str As String, sPattern As String, lSubMatch As Long) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = sPattern If re.test(str) = True Then Set mc = re.Execute(str) RegexMid = mc(0).submatches(lSubMatch - 1) End If End Function ================================= --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separation of text and numbers in an unmerged cell | Excel Discussion (Misc queries) | |||
Mean separation by LSD value | Excel Worksheet Functions | |||
name separation | Excel Worksheet Functions | |||
Imported text separation problem | Excel Discussion (Misc queries) | |||
text and number separation | Excel Discussion (Misc queries) |