CODEWITHSUNDEEP
excelvba
MK01111000
MK01111000

Reputation: 822

How do I split a string at the space character if not preceded by a comma

I am looking for a way to split text at the space character but only if the space character is not preceeded by a comma. The following are some examples of a strings

P3, 367 P5, 367 P5, 367

or

P3 P5 P5

or

P3, A6, A7 P4 P5 etc...

Getting them splitted at the empty space character is done as follows

arr = VBA.Split(ActiveCell.Value, " ")

The desired result is as follows:

[P3, 367 P5, 367 P5, 367]

or

[P3 P5 P5]

or

[P3, A6, A7 P4 P5] etc ...

Upvotes: 1

Views: 105

Answers (2)

beyarkay
beyarkay

Reputation: 1023

Since this is the first google result when searching for "split on a space not preceded by a comma", there's another option involving regex negative look behinds:

(?<!,) (note the space after the closing ))

This will match on all spaces that are not preceded by a comma.


import re

NO_COMMA_THEN_SPACE = r'(?<!,) '

split = re.split(
    NO_COMMA_THEN_SPACE, 
    'P3, 367 P5, 367 P5, 367'
)

assert split == ['P3, 367', 'P5, 367', 'P5, 367']

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Quick Workaround

Replace all ", " with something special that does not exist in the string like # to escape your ", " that you want to protect from split. So 

P3, 367 P5, 367 P5, 367

turns into

P3#367 P5#367 P5#367

Then replace spaces by something else unique eg % so it turns into

P3#367%P5#367%P5#367

Then revert the first replace of # backe into its original ", " so you get

P3, 367%P5, 367%P5, 367

And split by % to get 

P3, 367
P5, 367
P5, 367

Alternatively

Researach how to use regular expressions in VBA: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops.

Upvotes: 6

Related Questions