Extracting data seperated by spaces

[tdub4034]

i am working with a excel spreadsheet that has been converted from PDF. The conversion rendered merged cells in a column that has percentages separated by 5 spaces (i.e. 100% 50%). Is it possible to use RegEx to extract "50%" and place it in next column ("C")? An un-merged action frees up a column where I would like to store the second percentage (50%).LEN discovered 12 characters. Generally, I'd like to, Take the right side of columns B & E place that data in it's respective adjacent column. So far, all I have succeeded in is getting "Compile error: User-defined type not defined". I've swithced expressions with "Set regex = CreateObject("vbscript.regexp") - but to no success.
Below is the code I have tried for Column B.

Sub RegExTest()

    'CoInsurance "In" & "Out" percentages
    Dim arr As Variant
    arr = perNames.Range("B4").CurrentRegion.Columns(2).Value   

    'Create Regular Experession objevt
    Dim regEx As New RegExp
    regEx.Global = True
    regEx.Pattern = "[0-9]+"

    Dim text As Variant
    Dim mc As MatchCollection, row As Long
    row = 1
    
    'Read though each of the strings
    For Each text In arr

        If regEx.Test(text) = True Then
            Set mc = regEx.Execute(text)
            perNames.Range("C" & row).Value = mc(0)
            perNames.Range("D" & row).Vaue = mc(1)

        End If

 

[Rick Rothstein]

Does it have to use Regular Expressions?

Sub SplitInOut()
  ' Fixes the head/percentage combo problem in cells B2 and E2
  Range("B2:B3") = Application.Transpose(Split(Range("B2"), vbLf))
  Range("E2:E3") = Application.Transpose(Split(Range("E2"), vbLf))
  ' Splits the values as you indicated you wanted
  Range("B2", Cells(Rows.Count, "B").End(xlUp)).TextToColumns , xlDelimited, , True, False, False, False, True, False
  Range("E2", Cells(Rows.Count, "E").End(xlUp)).TextToColumns , xlDelimited, , True, False, False, False, True, False
End Sub

 

[tdub4034]

It does not have to use regex. I had tried using Split and the Left/Right methods but since the data format was different in other columns I decided to trash it. I thought that since the data was dynamic - Regex was the best way to handle it. I like the output but your code moves the sub headers "In","Out",Individual" & "Family" over one column too far - while also removing the zero ($0) amounts below "Deductible". Below is my desired output.

 

[Rick Rothstein]

That means your data is not "pure"... you have a space in front of the word "In" and you have trailing spaces after the last percent sign in each cell. This code will fix those problems and then output what you wanted...

Sub SplitInOut()
  Columns("B").Replace "% ", "%", xlPart, , , , False, False
  Range("B2:B3") = Application.Transpose(Split(Trim(Range("B2")), vbLf))
  Range("E2:E3") = Application.Transpose(Split(Trim(Range("E2")), vbLf))
  Range("B2", Cells(Rows.Count, "B").End(xlUp)).TextToColumns , xlDelimited, , True, False, False, False, True, False
  Range("E2", Cells(Rows.Count, "E").End(xlUp)).TextToColumns , xlDelimited, , True, False, False, False, True, False
End Sub

 

[tdub4034]

Looks perfect. You are correct. I thought to myself (after reading your reply) a "Trim" was in order but I see that you included it in your edit. So why did you dismiss Regex and opt for "vblf"/"TextToColumns"? I have a tendency to make things more difficult than it should be.

 

[Rick Rothstein]

So why did you dismiss Regex and opt for "vblf"/"TextToColumns"?

Two reasons... first, the code is simpler (in my opinion)... second, it has been nearly 30 years since I did anything with Regular Expressions so I really do not remember much about using them any more.