Parsing Data / Advanced Text to Columns for Non Uniform Data

Excel1991

Board Regular
Joined
Aug 1, 2018
Messages
56
I was hoping someone would be able to assist me with a question I have regarding a text-to-columns type scenario. I have copied names, number, address and titles from a website but when i past it into excel it all goes into column A. Normally, if the data was uniform and consistent, you could easily use text to columns.

The data i have is separated with various delimiters and is of varying lengths. Some of the cells contain all of the fields (name, address, number, title), while other may only have 2 of the field criteria (i.e. name and number).

I have tried to replace all the delimiters with a "," so i could attempt to separate it by text-to-columns. However, since the information in each field is not the same length I end up with a cell phone number in the "address" column or a address in the "position" column. There is too much data to manually clean this up after performing the text-to-columns

Does anyone happen to have a solution for parsing out this type of information? Any information would be helpful! Thank you!

COLUMN ANAMEPOSITIONEMAILPHONE
1Jack Drisko, Contracting Officer, Email dracccc@state.gov - Donald Johnson, Subject Matter Expert, Email Johnson@state.gov
2Larry Bird, Senior Contracting Officer, Phone 7038009999, Email BigLA@state.gov - Excel Help Team, Mailbox, Excel_Help_Team@state.gov
3Steph Curry, Phone 7038009999, Email stephcurry@state.gov - James Johnson, Contracting Officer, Phone 7038000999, Email Jonsonjix@state.gov
4Don McNabb, Email Mcnabb@state.gov
5Jack Ryan, Phone 7030998898 Email JackRRyan@state.gov - John M. Piecrce Contracting Officer , Phone 703-999-8897, Email Okaygoogle@state.gov

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,938
Office Version
2007
Platform
Windows
Taking case 3, how do you want the result:

Option A:
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:229.07px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:153.98px;" /><col style="width:36.12px;" /><col style="width:94.1px;" /><col style="width:118.81px;" /><col style="width:76.04px;" /><col style="width:192.95px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="text-align:left; ">NAME</td><td style="text-align:left; ">POSITION</td><td style="text-align:left; ">EMAIL</td><td style="text-align:left; ">PHONE</td><td > </td><td style="text-align:left; ">NAME</td><td style="text-align:left; ">POSITION</td><td style="text-align:left; ">EMAIL</td><td style="text-align:left; ">PHONE</td></tr><tr style="height:104px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:left; ">Steph Curry, Phone 7038009999, Email stephcurry@state.gov - James Johnson, Contracting Officer, Phone 7038000999, Email Jonsonjix@state.gov</td><td style="text-align:left; ">Steph Curry</td><td > </td><td style="text-align:left; ">7038009999</td><td style="color:#0000ff; text-decoration:underline; text-align:left; ">stephcurry@state.gov</td><td > </td><td style="text-align:left; ">James Johnson</td><td style="text-align:left; ">Contracting Officer</td><td style="text-align:left; ">7038000999</td><td style="color:#0000ff; text-decoration:underline; text-align:left; ">Jonsonjix@state.gov</td></tr></table>

Option B:
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:229.07px;" /><col style="width:94.1px;" /><col style="width:118.81px;" /><col style="width:73.19px;" /><col style="width:136.87px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="text-align:left; ">NAME</td><td style="text-align:left; ">POSITION</td><td style="text-align:left; ">EMAIL</td><td style="text-align:left; ">PHONE</td></tr><tr style="height:104px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:left; ">Steph Curry, Phone 7038009999, Email stephcurry@state.gov - James Johnson, Contracting Officer, Phone 7038000999, Email Jonsonjix@state.gov</td><td style="text-align:left; ">Steph Curry</td><td > </td><td style="text-align:left; ">7038009999</td><td style="color:#0000ff; text-decoration:underline; text-align:left; ">stephcurry@state.gov</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:left; ">James Johnson</td><td style="text-align:left; ">Contracting Officer</td><td style="text-align:left; ">7038000999</td><td style="color:#0000ff; text-decoration:underline; text-align:left; ">Jonsonjix@state.gov</td></tr></table>
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
One cell may contains 2 persons info, so the result will be in 1 row or 2 rows? For example: Can you show us what the result from cell A2 should look like?
How many rows is your data?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,938
Office Version
2007
Platform
Windows
I give you the code for option B.

Code:
Sub Parsing_Data()
  Dim c As Range, d As Variant, e, j As Long, l As Long, i As Long
  j = 2
  Range("B2:E" & Rows.Count).ClearContents
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
    For Each d In Split(c, " - ")
      If Not IsNumeric(WorksheetFunction.Trim(d)) Then
        e = Split(d, ",")
        Cells(j, "B") = Trim(e(0))
        For i = 1 To UBound(e)
          Select Case True
            Case InStr(1, e(i), "@")
            Case InStr(1, e(i), "Phone")
            Case Else
              Cells(j, "C") = Trim(e(i))
              Exit For
          End Select
        Next
        Call toColumn(e, "@", "D", j, 0)
        Call toColumn(e, "Phone", "E", j, 1)
        j = j + 1
      End If
    Next
  Next
End Sub


Sub toColumn(e, text, col, j, n)
  Dim ds, k As Long, i As Long
  For i = 1 To UBound(e)
    If InStr(1, e(i), text) > 0 Then
      ds = Split(e(i), " ")
      For k = 0 To UBound(ds)
        If InStr(1, ds(k), text) > 0 Then
          Cells(j, col) = Trim(ds(k + n))
        End If
      Next
    End If
  Next
End Sub
HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Parsing_Data) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

Excel1991

Board Regular
Joined
Aug 1, 2018
Messages
56
This works beautifully, Thank you!

It appears that sometimes the when its pulling the phone number it does not pull of the digits. For instance, when I run the macro on the actual data, it may only pull the first three digits of the phone number? the phone numbers in my source data have varying delimiters. The phone number is portray in different ways such as 703-999-9999, 7039999999, 703 999-9999.

Do you have any thoughts on why this may be occurring?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,938
Office Version
2007
Platform
Windows
This works beautifully, Thank you!

It appears that sometimes the when its pulling the phone number it does not pull of the digits. For instance, when I run the macro on the actual data, it may only pull the first three digits of the phone number? the phone numbers in my source data have varying delimiters. The phone number is portray in different ways such as 703-999-9999, 7039999999, 703 999-9999.

Do you have any thoughts on why this may be occurring?
You can put the examples where you have problems, to check if I can find a pattern and fix the macro.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,777
Office Version
365
Platform
Windows
One cell may contains 2 persons info, so the result will be in 1 row or 2 rows? For example: Can you show us what the result from cell A2 should look like?
How many rows is your data?
@Excel1991 I am also interested in your responses to Akuini's questions as your second sample data looks unusual in relation to what appears after the first email address.

I'm also interested in sample 5. Is it accurate? If it is accurate and the second name is "John M. Piecrce" and the position is "Contracting Officer" then having Excel split those two items will be difficult as there is no distinct delimiter between them.
Is there a fixed list of descriptions for "Position"? If so, could we have that list?

If your original sample data is not completely accurate or representative of the variations you have got, & it sounds like it is not representative given your comments about phone number formats, can we have a more representative set of sample data and the corresponding expected results?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,100,136
Messages
5,472,736
Members
406,834
Latest member
RahafKh

This Week's Hot Topics

Top