How do I deal with multiple spaces in a VBA string?-CodePudding

I am extracting words from a string in an Excel cell to a collection so that I can split to multiple columns. Some of the words have multiple spaces between them. Where this is the case I want to insert a blank column in my extracted split range, in other words I want to insert a blank item in my collection. I can extract the words just fine, but for some reason I cannot evaluate the instances with multiple spaces correctly. VBA seems to interpret any single space as multiple spaces.

I know that Range has the TextToColumns method, but it is not suitable for my needs.

Here is a small subset from the string:

Shift 2  Shift 1  Shift 2  Shift 1  Shift 2  Shift 1  Shift 2  Week 21  Week 22  Week 23  Week 24  Week 25  Week 26  Week 27  Week 28 Week 29  Week 30  Week 33  Week 34  Week 35           Week 27  Week 28 Week 29  Week 30  Week 33  Week 34  Week 35           Week 27  Week 28 Week 29  Week 30  Week 33  Week 34  Week 35           Week 27  Week 28 Week 29  Week 30  Week 33  Week 34  Week 35

An expected output for this subset would be a collection of the following items. There is an empty string between Week 35 and Week 27:

"Shift 2", "Shift 1", "Shift 2" ... "Week 34", "Week 35", " ", "Week 27", "Week28" ...

I have tried using InStr to find the multiple spaces: The increase in value for 'secondCaracterPos' is because it is part of a loop.

If InStr(secondCharacterPos, multioutput(Range("a3")), "          ") Then secondDateCollection.Add " ": secondCharacterPos = secondCharacterPos   10

as well as:

If InStr(secondCharacterPos, multioutput(Range("a3")), " ") And InStr(secondCharacterPos   1, multioutput(Range("a3")), " ") _
And InStr(secondCharacterPos   2, multioutput(Range("a3")), " ") And InStr(secondCharacterPos   3, multioutput(Range("a3")), " ") _
And InStr(secondCharacterPos   4, multioutput(Range("a3")), " ") And InStr(secondCharacterPos   5, multioutput(Range("a3")), " ") _
And InStr(secondCharacterPos   6, multioutput(Range("a3")), " ") And InStr(secondCharacterPos   7, multioutput(Range("a3")), " ") _
And InStr(secondCharacterPos   8, multioutput(Range("a3")), " ") And InStr(secondCharacterPos   9, multioutput(Range("a3")), " ") _
Then secondDateCollection.Add " ": secondCharacterPos = secondCharacterPos   10

I have also tried MID with both the short and long forms as above.

I suspect I am overlooking something really small. Any help would be much appreciated.

CodePudding user response：

This seems to work:

s = "Shift 2  Shift 1  Shift 2  Shift 1  Shift 2  Shift 1  Shift 2  Week 21  Week 22  Week 23  Week 24  Week 25  Week 26  Week 27  Week 28 Week 29  Week 30  Week 33  Week 34  Week 35           Week 27  Week 28 Week 29  Week 30  Week 33  Week 34  Week 35           Week 27  Week 28 Week 29  Week 30  Week 33  Week 34  Week 35           Week 27  Week 28 Week 29  Week 30  Week 33  Week 34  Week 35"
? """" & Replace(Replace(s, Space(11), ""","" "","""), Space(2), """,""") & """"
"Shift 2","Shift 1","Shift 2","Shift 1","Shift 2","Shift 1","Shift 2","Week 21","Week 22","Week 23","Week 24","Week 25","Week 26","Week 27","Week 28 Week 29","Week 30","Week 33","Week 34","Week 35"," ","Week 27","Week 28 Week 29","Week 30","Week 33","Week 34","Week 35"," ","Week 27","Week 28 Week 29","Week 30","Week 33","Week 34","Week 35"," ","Week 27","Week 28 Week 29","Week 30","Week 33","Week 34","Week 35"