# Telling Excel VBA TextToColumns NOT to split by spaces

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?

## Answers

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.