Parsing Data / Advanced Text to Columns for Non Uniform Data

[Excel1991]

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 A	NAME	POSITION	EMAIL	PHONE
1	Jack Drisko, Contracting Officer, Email dracccc@state.gov - Donald Johnson, Subject Matter Expert, Email Johnson@state.gov
2	Larry Bird, Senior Contracting Officer, Phone 7038009999, Email BigLA@state.gov - Excel Help Team, Mailbox, Excel_Help_Team@state.gov
3	Steph Curry, Phone 7038009999, Email stephcurry@state.gov - James Johnson, Contracting Officer, Phone 7038000999, Email Jonsonjix@state.gov
4	Don McNabb, Email Mcnabb@state.gov
5	Jack Ryan, Phone 7030998898 Email JackRRyan@state.gov - John M. Piecrce Contracting Officer , Phone 703-999-8897, Email Okaygoogle@state.gov

<tbody>
</tbody>

 

[DanteAmor]

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]

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]

I give you the code for option B.

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]

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]

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]

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?