Excel VBA TextToColumns calculation NOT space separated

Question

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

In cell A1, I have the following line:

AB,C 

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

But if I have this line:

 ABC,D 

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

Why is TextToColumns automatically split by spaces when there is more than one space, even if I am not explicitly talking about it? Is this a known bug? Is there a workaround besides manually parsing rows into columns?

Answer 1

You want to explicitly set the DataType to xlDelimited , otherwise Excel assumes that the data is arranged in fixed-width columns, and Excel first assumes how wide these columns are, where spaces - ignore any separators, select 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 the DataType argument:

 Range("A1").TextToColumns 

Note that the Excel documentation is erroneous in this regard: it says xlDelimited is the default, but it is clear that xlFixedWidth is the default value in reality.

So, a long story, what you need is the following:

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

EDIT It looks like the Excel documentation may be wrong. It really smells like a bug in Excel-VBA. See the discussion in the comments below.

Answer 2

Set ConsecutiveDelimiter to True :

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