1

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 ...

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
MK01111000
  • 770
  • 2
  • 9
  • 16

2 Answers2

6

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • I've tried using `RegEx` but your first suggestion might just be what OP needs for simplicity: `arr = Application.Substitute(Split(Replace(, ", ", "%"), " "), "%", ", ")` – JvdV Feb 12 '20 at 10:17
  • Thanks Peh, that did the job! – MK01111000 Feb 12 '20 at 10:23
0

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']
beyarkay
  • 513
  • 3
  • 16