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

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
45
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
455
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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: 5
  • powerQuery-TextDelimiter-Excel Output.JPG
    powerQuery-TextDelimiter-Excel Output.JPG
    98.9 KB · Views: 6
  • powerQuery-TextDelimiter-Step1.JPG
    powerQuery-TextDelimiter-Step1.JPG
    115.1 KB · Views: 6
  • powerQuery-TextDelimiter-Step2.JPG
    powerQuery-TextDelimiter-Step2.JPG
    132.1 KB · Views: 6
  • powerQuery-TextDelimiter-Step3.JPG
    powerQuery-TextDelimiter-Step3.JPG
    139.8 KB · Views: 5
  • powerQuery-TextDelimiter-Step4.JPG
    powerQuery-TextDelimiter-Step4.JPG
    103.1 KB · Views: 5

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,453
Office Version
  1. 365
Platform
  1. Windows
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
 

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
 

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 5, 2021
Messages
1,145
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 4, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
 

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,145
Office Version
  1. 2010
Platform
  1. Windows
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 …​
 

Forum statistics

Threads
1,136,768
Messages
5,677,624
Members
419,707
Latest member
Anna vib

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
Top