Take the 2-minute tour ×

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:


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:


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?


share|improve this question
This question is interesting because it exposes a likely Excel-VBA bug. –  Jean-François Corbett Dec 1 '11 at 8:02
add comment

2 Answers

up vote 6 down vote accepted

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:


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.

share|improve this answer
Aha, xlDelimited is what I wanted! Thanks! –  Josh Nov 30 '11 at 19:58
That certainly fixes it, but I don't think it's the whole story. If I omit DataType and don't used named arguments, I get the expected behavior. Also, help says re Comma "...True to have DataType be xlDelimited and to have the comma be a delimiter...", which makes me think setting comma to True would force xlDelimited. Clearly that's not the case. It seems that named arguments sets a default that's different than when not using named arguments. Sounds like a bug to me. –  Dick Kusleika Nov 30 '11 at 20:07
Bug indeed, and I actually think it's even worse and weirder than what you describe. See my 2nd comment to JP's answer below. The take-home message is probably to used named arguments (non-named arguments clearly lead to unpredictable behaviour) and never assume that the defaults will work, so specify as many named arguments as necessary to make your tests work properly. –  Jean-François Corbett Nov 30 '11 at 20:16
After some more testing, the only parameter I actually need (at least for my purposes) is DataType:=xlDelimited . Comma and Space do not need to be explicitly set anymore. Perfect! –  Josh Nov 30 '11 at 22:35
That's nice. But to keep your code readable, I would advise to set things explicitly, so the next person reading your code (maybe you next year) knows exactly what the intent is. –  Jean-François Corbett Dec 1 '11 at 7:26
show 1 more comment

Set ConsecutiveDelimiter to True:

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

share|improve this answer
But this doesn't make any difference... Did you test before posting?! –  Jean-François Corbett Nov 30 '11 at 19:44
My bad, fixing code now. Yes I did test it, but I didn't use named parameters. My attempt to convert it to named parameters obviously failed. –  JimmyPena Nov 30 '11 at 19:52
Hm, this highly unreadable statement does "work", even though there is no rational explanation for why -- and converting the argument list to named parameters makes it not work... Smells like an Excel bug, and not like a recommendable answer. P.S. downvote isn't mine even though I was initially tempted. –  Jean-François Corbett Nov 30 '11 at 20:01
That's probably why it failed when I posted it using named parameters. Can't explain it. +1 for your answer, it's better anyway. –  JimmyPena Nov 30 '11 at 20:16
And thanks to whomever dinged me for what is ultimately a problem with Excel, not my code. –  JimmyPena Nov 30 '11 at 20:42
show 1 more comment

Your Answer


