告诉Excel VBA TextToColumns不要按空格拆分 [英] Telling Excel VBA TextToColumns NOT to split by spaces

问题描述

我在Excel 2003中使用VBA。
这是我遇到的问题的最简单的例子。
我要将 的字符串拆分成行中的单元格。

在单元格A1中，字符串：

  AB，C 
  

$ b b

在VBA中，如果我说 Range（A1）。TextToColumns Comma：= True，Space：= False 。单元格A1是 AB ，单元格B1是 C

但如果我有这个字符串：

  ABC，D 
  

那么如果我使用 Range（A1）TextToColumns Comma：= True，Space：= False 作者： SPACES 单元格A1是 A ，单元格B1是 B ，单元格C1是 C， code>（？！）

为什么 TextToColumns 空间，即使我明确地不告诉它吗？这是一个已知的错误？
是否有解决方法，除了手动解析字符串到我自己的列？

解决方案

您要显式设置 DataType 为 xlDelimited ，否则Excel假定您的数据排列在固定宽度的列中，而Excel的初始猜测这些列的宽度是空格的位置 - 忽略您在参数列表中选择的任何分隔符。

尝试以下操作，您会看到它重现您的结果：

  Range（A1）。TextToColumns DataType：= xlFixedWidth 
  

DataType 参数：

  Range（A1）TextToColumns $请注意，Excel文档在这方面是错误的：它说 xlDelimited  
 code>是默认值，但显然 xlFixedWidth 是实际中的默认值。 
 
 
 
所以，长话短说，你想要的是这样：
  Range（A1）。TextToColumns DataType：= xlDelimited，Comma：= True，Space：= False 
  
 编辑它看起来像一个比Excel文档多一点可能是错误的。这实际上闻起来像一个bug在Excel-VBA。请参阅下面的注释中的讨论。 
 
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?  
解决方案
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. 

                        
这篇关于告诉Excel VBA TextToColumns不要按空格拆分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！