How to separate a line of alternating Text/Numbers into Columns

tge496

New Member
Joined
Jul 19, 2009
Messages
3
I have thousands of lines of data that are of the following form:

Jackson, TN 9623 BCBS TN 98 UnitedHlthCare 2<o:p></o:p>
Jacksonville, FL 3577 Aetna 47 BCBS FL 37<o:p></o:p>
Knoxville, TN 3796 Cariten 44 John Deere 41<o:p></o:p>
Lakeland-Winter Haven, FL 2165 UnitedHlthCare 31 Aetna 29


I need to separate these lines into columns such that, for example, the first line would be in 6 columns: (Jackson, TN) (9623) (BCBS TN) (98) (UnitedHlthCare) (2)


The delimiter does not work because some of there is no one character that always separates the text and the numbers. I've tried going through in word and typing something like '%' where I want to separate, but with thousands of lines of data that is extremely tedious.


Is there any formula I can use that would be capable of solving this?


Thanks much!


-Tom
<o:p></o:p>
<!--EndFragment-->
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
If you can tell us what rules you use to separate your columns, then we can create a formula/macro that can separate them. If there are no rules, then there's no way that a computer can do it properly.
 

tge496

New Member
Joined
Jul 19, 2009
Messages
3
Hi Sal

Thanks for your reply. I'm an Excel beginner to I'm not entirely sure what direction you're pointing me in. I apologize for providing insufficient information. The data I am working with is from a series of annual PDFs released by the American Medical Association.

The data is located in a table form, with multiple tables sporadically across the PDF. I have been copying the text directly from the tables in the PDF, and pasting it either in Excel (where it auto-separates using a space as a delimiter) or Word (where each row, containing 6 columns, is combined into one).

With this information, is there something you could suggest? Should I export the PDF to HTML form?

Thanks again for your help.
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
If it's in tables in the PDF/available from the net, there's a large chance pre-formatted data exists somewhere. However, to use Excel to divvy up the info, you would need to create rules about how we know where to separate the data. Computers can do a lot if you can tell them what to do, but they are very poor at 'winging it' and knowing how to divvy up the data without very specific instructions.

So if you have logic like:
Column 1: Text up until the first group of numbers
Column 2: First group of numbers
Column 3: Between the first and second group of numbers
Column 4: Second group of numbers
Column 5: Between the second and third group of numbers
Column 6: Last number

Then we can do it. But if that isn't the rule, or if the rule changes by entry, then it won't be something we can easily write code for.
 

tge496

New Member
Joined
Jul 19, 2009
Messages
3
You nailed it! That is the exact set of rules used in all of the tables. Is there a code that can separate it as such?
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
There are formulas/macros that can do it. But my brain is failing me at the moment. Something with MID, ISNUMBER, and a lot of parentheses would probably do it.
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
Column A
Jackson, TN 9623 BCBS TN 98 UnitedHlthCare 2
Jacksonville, FL 3577 Aetna 47 BCBS FL 37
Knoxville, TN 3796 Cariten 44 John Deere 41
Lakeland-Winter Haven, FL 2165 UnitedHlthCare 31 Aetna 29

1] Assume your data housed at Column A

2] B1 , enter formula and copy down

=MID(A1,1,FIND(",",A1)+4)

3] C1 , enter formula copy across to H1 and fill down

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,$B1,"")," ",REPT(" ",50)),COLUMN(A1)*50-49,50))

Regards
Bosco
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,784
Perhaps something like this.
The UDF can be entered in the sheet as an array function.
Code:
Sub test()
    Dim oneCell As Range
    Dim dataArray As Variant
    With ThisWorkbook.Sheets("Sheet1").Range("A:A"): Rem adjust
        For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            dataArray = AlphaNumSplit(oneCell.Text)
            oneCell.Offset(0, 1).Resize(1, UBound(dataArray)).Value = (dataArray)
        Next oneCell
    End With
End Sub

Function AlphaNumSplit(inputstring As String) As Variant
    Dim splitStringArray As Variant
    Dim workString As String
    Dim i As Long, pointer As Long
    
    splitStringArray = Split(inputstring, " ")
    For i = 0 To UBound(splitStringArray)
        If IsNumeric(splitStringArray(i)) Then
            splitStringArray(pointer) = workString
            
            pointer = pointer + 1
            splitStringArray(pointer) = splitStringArray(i)
        
            workString = vbNullString
            pointer = pointer + 1
        Else
            workString = workString & splitStringArray(i)
        End If
    Next i
    
    splitStringArray(pointer) = workString
    pointer = pointer + (workString = vbNullString)
    
    ReDim Preserve splitStringArray(0 To pointer)
    AlphaNumSplit = splitStringArray
End Function
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
bosco, unfortunately that doesn't work out to the 6 columns desired...
1] b1

=mid(a1,1,find(",",a1)+4)

2] c1

=-lookup(2,-left(substitute(a1,b1,""),row($1:$99)))

3] d1

=mid(substitute(a1,b1&c1&" ",""),1,min(find({0,1,2,3,4,5,6,7,8,9},substitute(a1,b1&c1&" ","")&1234567890))-2)

4] e1

=-lookup(2,-left(substitute(a1,b1&c1&" "&d1,""),row($1:$99)))

5] f1

=mid(substitute(a1,b1&c1&d1&" ",""),1,min(find({0,1,2,3,4,5,6,7,8,9},substitute(a1,b1&c1&d1&" ","")&1234567890))-2)

6] g1

=-lookup(2,-right(a1,row($1:$99)))
 
Last edited:

Forum statistics

Threads
1,081,747
Messages
5,361,038
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top