VBS将一列拆分为两列 [英] VBS to split one column in two columns

问题描述

比如说，我有一列填满了名字，每个名字都在一个单元格中.

Let's say for example, i have one column filled with names, each name in one cell.

    First_Name_1 Last_Name_1
    First_Name_2 Last_Name_2
    First_Name_3 Last_Name_4

名字和姓氏用空格分隔.如何使用 Visual Basic 脚本将此列拆分为两列，以便在一列中包含 First_Name，在其旁边的列中包含 Last_name?

First name and last name are separated by space. How can i split this column in two columns, using Visual Basic Script so that i will have First_Name in one column and Last_name in a column next to it?

已经尝试过此代码，但无法使其工作.

Already tried this code, but can't manage to make it work.

    objExcel.ActiveSheet.Columns(4).Select
    Do Until IsEmpty(ActiveCell)
    'Search for position of space within the cell
    SplitPoint = InStrRev(ActiveCell, " ", -1, vbTextCompare)
    'Put the last name in the column next to the source column
    ActiveCell.Offset(0, 1) = Trim(Left(ActiveCell, SplitPoint))
    'Replace the source column with the first name
    ActiveCell.Offset(0, 0) = Trim(Mid(ActiveCell, SplitPoint))
    Loop

提前致谢！

推荐答案

Excel 有一个 TextToColumns() 函数，该函数可以通过单个函数调用完成您要查找的内容.

Excel has a TextToColumns() function that can do what you're looking for with a single function call.

objExcel.ActiveSheet.Columns(4).TextToColumns , , , , , , , True

显示的参数可能更有意义.您的数据以空格分隔，因此我们只需将第 8 个参数设置为 True.其余的可以省略以接受默认值.

Might make more sense with the params shown. Your data is space-delimited so we just need to set the 8th param to True. The rest can be omitted to accept the defaults.

objExcel.ActiveSheet.Columns(4).TextToColumns _
    , _        ' Destination (optional, defaults to source)
    , _        ' DataType (optional, defaults to xlDelimited)
    , _        ' TextQualifier (optional, defaults to xlTextQualifierDoubleQuote)
    , _        ' ConsecutiveDelimiter
    , _        ' Tab-delimited? (optional, defaults to False)
    , _        ' Semicolon-delimited? (optional, defaults to False)
    , _        ' Comma-delimited? (optional, defaults to False)
    True       ' Space-delimited?

这篇关于VBS将一列拆分为两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！