Telling Excel VBA TextToColumns NOT to split by spaces

Question

I am using VBA in Excel 2003. Here is the simplest example of the problem I am having. I want to split a string by commas only, into cells in the row.

In Cell A1, I have the following string:

A B,C

In VBA, if I say Range("A1").TextToColumns Comma:=True,Space:=False, it does what I expect. Cell A1 is A B and Cell B1 is C

But if I have this string:

A B C,D

then if I use Range("A1").TextToColumns Comma:=True,Space:=False, it splits by SPACES anyway! Cell A1 is A, Cell B1 is B, Cell C1 is C,D (?!)

Why is TextToColumns automatically splitting by spaces when there are more than one space, even when I explicitly tell it not to? Is this a known bug? Is there a workaround, other than manually parsing the strings into columns myself?

Answer 1

You want to explicitly set DataType to xlDelimited, else Excel assumes you data is arranged in fixed-width columns, and Excel's initial guess at how wide those columns are is where the spaces are -- ignoring whatever delimiters you choose in the argument list.

Try the following and you will see that it reproduces your results:

Range("A1").TextToColumns DataType:=xlFixedWidth

which gives the same results as omitting the DataType argument:

Range("A1").TextToColumns

Note that the Excel documentation is erroneous in this respect: it says xlDelimited is the default, but clearly xlFixedWidth is the default in reality.

So, long story short, what you want instead is this:

Range("A1").TextToColumns DataType:=xlDelimited, Comma:=True, Space:=False

EDIT It looks like a little more than the Excel documentation may be wrong. This actually smells like a bug in Excel-VBA. See discussion in the comments below.

Answer 2

Set ConsecutiveDelimiter to True:

Range("A1").TextToColumns ,,,True,,,True,False