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

[tge496]

I have thousands of lines of data that are of the following form:

Jackson, TN 9623 BCBS TN 98 UnitedHlthCare 2<o></o>
Jacksonville, FL 3577 Aetna 47 BCBS FL 37<o></o>
Knoxville, TN 3796 Cariten 44 John Deere 41<o></o>
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></o>
<!--EndFragment-->

 

[Sal Paradise]

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]

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]

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]

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]

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]

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

 

[Sal Paradise]

Bosco, unfortunately that doesn't work out to the 6 columns desired...

 

[mikerickson]

Perhaps something like this.
The UDF can be entered in the sheet as an array function.

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]

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)))