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.

 

[Marc L]

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 …​

 

[BalloutMoe]

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]

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]

How could I make it read the (K)MOBIL

Are you saying that the following is no longer, or never was, a possibility?

however one of the "MIGHTY" Conditions contains a (K) before it

 

[Peter_SSs]

How could I make it read the (K)MOBIL

Assuming the (K) could only possibly precede MOBIL then try altering the Pattern line to

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]

Thank you so much, \(K\) worked perfectly.

Assuming the (K) could only possibly precede MOBIL then try altering the Pattern line to

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]

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

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]

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]

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]

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.