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

BalloutMoe

Board Regular
Joined
Jun 4, 2021
Messages
137
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi,

You can use Power Query (see screenshot - solution)

Watch this video to learn Power Query in simple steps.

Thanks,
Saurabh
 

Attachments

  • powerQuery-TextDelimiter.JPG
    powerQuery-TextDelimiter.JPG
    151.5 KB · Views: 15
  • powerQuery-TextDelimiter-Excel Output.JPG
    powerQuery-TextDelimiter-Excel Output.JPG
    98.9 KB · Views: 16
  • powerQuery-TextDelimiter-Step1.JPG
    powerQuery-TextDelimiter-Step1.JPG
    115.1 KB · Views: 14
  • powerQuery-TextDelimiter-Step2.JPG
    powerQuery-TextDelimiter-Step2.JPG
    132.1 KB · Views: 12
  • powerQuery-TextDelimiter-Step3.JPG
    powerQuery-TextDelimiter-Step3.JPG
    139.8 KB · Views: 13
  • powerQuery-TextDelimiter-Step4.JPG
    powerQuery-TextDelimiter-Step4.JPG
    103.1 KB · Views: 15
Upvote 0
Not sure this is particularly efficient if data is large, but you could give it a try

VBA Code:
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
 
Upvote 0
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.
Untitled.jpg
Hi,

You can use Power Query (see screenshot - solution)

Watch this video to learn Power Query in simple steps.

Thanks,
Saurabh
 
Upvote 0
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.
 
Upvote 0
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 …​
 
Upvote 0
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 …​
I attached a sample file of the pdf and an excel sheet with specified columns I am looking for. Thank you
 
Upvote 0
Not sure this is particularly efficient if data is large, but you could give it a try

VBA Code:
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
VBA Code:
  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
 
Upvote 0
VBA Code:
  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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top