vba Excel TextToColumns 固定宽度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27974621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel TextToColumns FixedWidth
提问by Steven Martin
When using text to columns fixed width, what happens to to the spaces that are used to separate the columns in some files? ( The layout is padded with spaces but also a space separating the columns)
将文本用于固定宽度的列时，某些文件中用于分隔列的空格会发生什么变化？（布局用空格填充，但也用空格分隔列）
Per Row, the data for each column begins on and ends on or before the following table with another space separating each column. My question is , Should that space be part of the preceding or following column. MS Documentation doesn't even mention about the trimming of whitespace for fixed width at all.
每行，每列的数据在下表中或之前开始和结束，并用另一个空格分隔每列。我的问题是，该空间应该是前一列或后一列的一部分。MS 文档甚至根本没有提到修剪固定宽度的空白。
CHAR START CHAR MAX END( spaces will pad out to this char position if data doesn't)
1 12
14 48
50 52
54 63
65 68
70 73
75 89
91 102
104 138
140 142
144 EOL
This is the code I am using but I am wondering should all char positions after the first column be increased by 1 to be reliable..
这是我正在使用的代码，但我想知道是否应该将第一列之后的所有字符位置都增加 1 以确保可靠。
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(12, 1), Array(48, 1), Array(52, 1), Array(63, 1), _
Array(68, 1), Array(73, 1), Array(89, 1), Array(102, 1), Array(138, 1), Array(142, 1)), _
TrailingMinusNumbers:=True
SAMPLE DATA WOULD BE
样本数据将是
111111111111 AB CDEFGH IJKLMNO PQRSTYV WXYZA 1 11.1 ABC 1 1 ABCDEFGHIJKLMNO 111111111111 AB CDEFGH IJKLMNO PQSTYVW 11 100 ABC
回答by n8.
I would not add any numbers unless your data is being cut off at the right, or you have leading white spaces in any of your cells.
除非您的数据在右侧被截断，或者您的任何单元格中都有前导空格，否则我不会添加任何数字。
In your example, you have your second column starting at position 12. Column 1 has twelve "1's" and no trailing blank space.
在您的示例中，您的第二列从位置 12 开始。第 1 列有十二个“1”并且没有尾随空格。
Note that the second digit in "Array(0, 1)" denotes the data type. 1 = General, 2 = Text, 3 and above are all date types I believe. If you choose general for all of them (which you have there) it will automatically eliminate trailing blanks on numbers, converting that value to a number. If the column value contains any text at all it will be considered text. If you select "2" for all of these fields it will save your columns as text without any conversion, and the trailing blanks will not be eliminated from numeric values.
请注意，“Array(0, 1)”中的第二个数字表示数据类型。1 = 一般，2 = 文本，3 及以上都是我相信的日期类型。如果您为所有这些选择通用（您在那里拥有），它将自动消除数字的尾随空白，将该值转换为数字。如果列值包含任何文本，它将被视为文本。如果您为所有这些字段选择“2”，它会将您的列保存为文本而不进行任何转换，并且不会从数值中消除尾随空格。
回答by pnuts
When using text to columns fixed width, what happens to to the spaces that are used to separate the columns in some files?
将文本用于固定宽度的列时，某些文件中用于分隔列的空格会发生什么变化？
They are removed. The equivalent of TRIM (worksheet function) being applied to each field (the content between the column breaks). 345....DEF(where the dots here represent spaces) ends up as 345in one cell and DEFin another wherever the divider is placed between 5and D. This is so regardless of the Column data format chosen and regardless of whether the characters are numeric or alphabetic.
它们被移除。相当于 TRIM（工作表函数）应用于每个字段（分栏之间的内容）。345....DEF（这里的点代表空格）345在一个单元格中结束，而在另一个单元格DEF中，分隔线放置在5和之间D。无论选择的列数据格式如何，也无论字符是数字还是字母，都是如此。
As long as your numbers for parsing represent a position next to or within a region of one or more blank spaces between the fields you want separated it makes no difference whether at the beginning of one or more spaces, in amongst one or more spaces either side or at the end of one or more spaces. And no difference whether one space or one hundred.
只要您的解析数字代表您想要分隔的字段之间的一个或多个空格的区域旁边或内的位置，无论是在一个或多个空格的开头，还是在任一侧的一个或多个空格中，都没有区别或在一个或多个空格的末尾。无论是一个空格还是一百个空格都没有区别。
