Excel VBA Parse Column A into multiple columns help but with conditions

[BalloutMoe]

Hello, I have a TXT file that gets imported in excel and it always ends up in column A. I am trying to split the data up however. The middle columns with text usually vary. For example

1.000 1.000 EA MWPTS16 MIGHTY 16in TecSelect WIPER BLADE EA 1.9900 1.99

1.000 1.000 EA MWPTS18 MIGHTY 18in TecSelect WIPER BLADE EA 1.9900 1.99

1.000 1.000 EA MWPTS21 MIGHTY 21in TecSelect WIPER BLADE EA 1.9900 1.99

1.000 1.000 EA MLE 9012LL MIGHTY HIR2/12V LONG LIFE HALOGEN 16.9800 16.98

EA

1.000 1.000 EA MLE H11-55W MIGHTY HALOGEN HEADLAMP EA 7.9500 7.95

4.000 4.000 EA MSL 80-39 MIGHTY DRAIN PLUG EA 4.9500 19.80

1.000 1.000 EA MFPTC2064 MIGHTY TECSELECT CABIN AIR FILTER 5.4900 5.49

EA

I would like to split like this: 1.000 | 1.000 EA | rest of the text here | Then 1.9900 | 1.99. Ending up with 5 columns. However the length of the text varies every time. So if I split them normally some number won't be inline with each other. Can this be done in any specific way. Or any way to guide me get started.

 

[Saurabhj]

Hi,

You can use Power Query (see screenshot - solution)

Watch this video to learn Power Query in simple steps.

Thanks,
Saurabh

 

[Peter_SSs]

Not sure this is particularly efficient if data is large, but you could give it a try

Sub Split5()
  Dim a As Variant
  Dim i As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
  For i = 1 To UBound(a)
    a(i, 1) = StrReverse(Replace(StrReverse(Replace(Replace(Replace(a(i, 1), " ", ";", 1, 3), ";", " ", 1, 2), " ", ";", 1, 1)), " ", ";", 1, 2))
  Next i
  Application.ScreenUpdating = False
  With Range("B2").Resize(UBound(a))
    .Value = a
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Resize(, 5).Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

 

[BalloutMoe]

Thank you for your reply and help. The problem is its a whole text file that contains shop name, company name, invoice numbers on top and what not. It comes as a pdf file and I am trying to extract it into excel and having a hard time with it. Because each invoice sometimes is a different sized table.

Hi,

You can use Power Query (see screenshot - solution)

Watch this video to learn Power Query in simple steps.

Thanks,
Saurabh

 

[BalloutMoe]

Thank you for your help. This worked for the first two columns but not the last two. for the amounts. I will keep digging down and see how I can adapt it better.

 

[Marc L]

Hello, I have a TXT file that gets imported in excel and it always ends up in column A.

Hi, I have differents ways so in order to test & choose the more appropriate I need you zip a source text file and the expected result workbook accordingly​

on a files host website like Dropbox for example …​

 

[BalloutMoe]

Hi, I have differents ways so in order to test & choose the more appropriate I need you zip a source text file and the expected result workbook accordingly​

on a files host website like Dropbox for example …​

Dropbox

www.dropbox.com

I attached a sample file of the pdf and an excel sheet with specified columns I am looking for. Thank you

 

[BalloutMoe]

Not sure this is particularly efficient if data is large, but you could give it a try

Sub Split5()
  Dim a As Variant
  Dim i As Long
 
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
  For i = 1 To UBound(a)
    a(i, 1) = StrReverse(Replace(StrReverse(Replace(Replace(Replace(a(i, 1), " ", ";", 1, 3), ";", " ", 1, 2), " ", ";", 1, 1)), " ", ";", 1, 2))
  Next i
  Application.ScreenUpdating = False
  With Range("B2").Resize(UBound(a))
    .Value = a
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Resize(, 5).Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

  For i = 1 To UBound(a)
    a(i, 1) = StrReverse(Replace(StrReverse(Replace(Replace(Replace(a(i, 1), " ", ";", 1, 3), ";", " ", 1, 2), " ", ";", 1, 1)), " ", ";", 3, 1))
  Next i

I adjusted it from 1,2 to 3,1 to get this "1.000; 1.000;EA MFPA81680 MIGHTYAIR FILTER EA 6.4900 ;6.49"
I tried adding another replace but I am not able to figure out the logic behind it. I would like to add a semi colon before the 6.4900 as well. So it will be
"1.000; 1.000;EA MFPA81680 MIGHTYAIR FILTER EA ;6.4900 ;6.49"

Thank you

 

[BalloutMoe]

  For i = 1 To UBound(a)
    a(i, 1) = StrReverse(Replace(StrReverse(Replace(Replace(Replace(a(i, 1), " ", ";", 1, 3), ";", " ", 1, 2), " ", ";", 1, 1)), " ", ";", 3, 1))
  Next i

I adjusted it from 1,2 to 3,1 to get this "1.000; 1.000;EA MFPA81680 MIGHTYAIR FILTER EA 6.4900 ;6.49"
I tried adding another replace but I am not able to figure out the logic behind it. I would like to add a semi colon before the 6.4900 as well. So it will be
"1.000; 1.000;EA MFPA81680 MIGHTYAIR FILTER EA ;6.4900 ;6.49"

Thank you

I was able to get that figured out by trial and error

 

[Marc L]

I attached a sample file of the pdf and an excel sheet with specified columns I am looking for.

As in the initial post you wrote a text file so very not a pdf file …​