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

BalloutMoe

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

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows
Sheet1(INVOICEDATA) tried it here with error on the same file
As INVOICEDATA is not the sheet name of your last attachment (post #14) so try first with it …​
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
I misunderstood previously that the parentheses around the K were literal and not part of the RX pattern syntax.

So, you are now saying we could have MIGHTY or DRAIN or MOBIL?
Is that the full list?

Are you also saying that any of those could be prefixed by (K)?

Any other possible prefixes? eg K by itself or something else in parentheses?
If you look at the code I posted I have few other criteria. MIGHTY SHELL VALVOLINE ect, my code is working fine. It’s just this one line with
(K) in front of the Mobil. Their is lines that have MOBIL but no (K) in front of them. I am having issues with the ones that contain (K)MOBIL
 

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
RX.Pattern = "^([^ ]+)( )([^ ]+)( )(EA |CS |GL )(.+?)( )(MIGHTY|GOLDEN|MOTORCRAFT|MOBIL|VALVOLINE|COMPLETE|SHELL|BRAKE|CHEVRON|ROYAL|CASTROL|ANMEX|PENNZOIL|WINDSHIELD|UNIVERSAL)(.+)( )([^ ]+)( )([^ ]+)$"

How could I make it read the (K)MOBIL
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,170
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How could I make it read the (K)MOBIL
Assuming the (K) could only possibly precede MOBIL then try altering the Pattern line to
VBA Code:
RX.Pattern = "^([^ ]+)( )([^ ]+)( )(EA |CS |GL )(.+?)( )(MIGHTY|GOLDEN|MOTORCRAFT|\(K\)MOBIL|MOBIL|VALVOLINE|COMPLETE|SHELL|BRAKE|CHEVRON|ROYAL|CASTROL|ANMEX|PENNZOIL|WINDSHIELD|UNIVERSAL)(.+)( )([^ ]+)( )([^ ]+)$"

If the (K) could also possibly precede all of the other words in that list then rather than doubling up all the terms like I have with MOBIL, a different approach could be used.
 

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Thank you so much, \(K\) worked perfectly.
Assuming the (K) could only possibly precede MOBIL then try altering the Pattern line to
VBA Code:
RX.Pattern = "^([^ ]+)( )([^ ]+)( )(EA |CS |GL )(.+?)( )(MIGHTY|GOLDEN|MOTORCRAFT|\(K\)MOBIL|MOBIL|VALVOLINE|COMPLETE|SHELL|BRAKE|CHEVRON|ROYAL|CASTROL|ANMEX|PENNZOIL|WINDSHIELD|UNIVERSAL)(.+)( )([^ ]+)( )([^ ]+)$"

If the (K) could also possibly precede all of the other words in that list then rather than doubling up all the terms like I have with MOBIL, a different approach could be used.
 

BalloutMoe

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

ADVERTISEMENT

I am trying to understand better what each symbol does and how the pattern is structured. If I can get a description if possible just to help me down the road.
1.000 1.000 EA MFPA90121 MIGHTYAIR FILTER EA 29.9900 29.99
VBA Code:
RX.Pattern = "^([^ ]+)( )([^ ]+)( )([^ ]+ )([^ ]+)( )(.+)( )([^ ]+)( )([^ ]+)$"

If I had something like this for example and trying to take columns 1,3,4,6,7. What pattern does this represent? Trying to understand the logic behind the "^([^ ]+)( )" and what not.
SH2312B 210 50.0000 50.00 0.0000 11.15 557.50

Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,170
Office Version
  1. 365
Platform
  1. Windows
Thank you so much, \(K\) worked perfectly.
Good news! :)

If I can get a description if possible ..
Regular expressions take quite a bit to get your head around (for me anyway) & I don't think I can give a particularly useful outline in a short time here. I suggest that you do some research about them & I have given just a few links below but you would find many more. One thing to note though is that regular expressions are used in many languages and the particular implementations are not all the same so what you may read in some places may not necessarily apply to using reg ex in vba.
Excel Regex Tutorial (Regular Expressions) - Analyst Cave
Regular Expressions Quick Start
regex101: build, test, and debug regex

When I was learning I did purchase this book, though I note that it has now been replaced - link in the article.
 

BalloutMoe

New Member
Joined
Jun 4, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Good news! :)


Regular expressions take quite a bit to get your head around (for me anyway) & I don't think I can give a particularly useful outline in a short time here. I suggest that you do some research about them & I have given just a few links below but you would find many more. One thing to note though is that regular expressions are used in many languages and the particular implementations are not all the same so what you may read in some places may not necessarily apply to using reg ex in vba.
Excel Regex Tutorial (Regular Expressions) - Analyst Cave
Regular Expressions Quick Start
regex101: build, test, and debug regex

When I was learning I did purchase this book, though I note that it has now been replaced - link in the article.
Thank you for the links I will be reading through them.
SH2312B 210 50.0000 50.00 0.0000 11.15 557.50

What would be a pattern for this above line if I want to group each item?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,170
Office Version
  1. 365
Platform
  1. Windows
if I want to group each item?
Not certain what you mean by "group each item". If you mean to put each item (term?) into its own column like we have been doing above then you wouldn't use any reg ex pattern at all - just Text to Columns with a space delimiter.

If you mean something else then please clarify and include 'before' and 'after' examples.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,757
Members
418,411
Latest member
Excellency

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