☰

使用宏将数据拆分为两列

tag-icon • tag-icon microsoft-excel macros
使用宏将数据拆分为两列

使用宏将数据分成两列。

在 Windows 10 笔记本电脑上使用 Excel 2013。曾在 Word 中使用过宏，但在 Excel 中使用宏还是新手。

目标是将单元格中第一个单词以外的所有内容（可能包含多个单词，但单词数量不一定相同）“移动”到同一行、下一列。例如，这里有一个简单的表格：

Cell B2:  Tom This is a test.
Cell B3:  Dick This is another test.
Cell B4:  Harry This is a third test.

例如，将光标放在 B2 中并运行宏时，应该将“Tom”留在 B2 中，将“This is a test.”剪切并粘贴到 C2 中，然后向下移动一行并向左移动一列到单元格 B3 以准备对“Dick This is another test.”执行相同操作。尽管此时，如果我不想在 B3 上执行宏，我可以例如手动将光标向下移动到 B4，并在那里运行宏，将“Harry”留在 B4 中并将“This is a third test.”移动到 C4。

以下是我尝试过的办法（结果/问题如下）：

将光标放在包含“Tom This is a test”的起始单元格（假设为 B2）。单击“开发人员”选项卡。确保已激活“使用相对引用”。录制宏。根据需要为其命名、分配快捷键并输入说明。将宏存储在此工作簿中。按 F2 进行编辑。进入编辑模式，光标位于条目的右端。（请记住，我预先将光标放在 B2 中。）按 Home 移动到单元格的开头（左侧）。Ctrl+向右箭头越过名字和空格键，移动到“T”his is a test 中的“T”。按住 Shift 键并按 End 选择“This is a test”。按 Ctrl+X 进行剪切。按 Tab 移动到单元格 C2。按 Ctrl+V 将“This is a test”粘贴到单元格 C2 中。按 Enter 向下移动一行并向左移动一列到单元格 B3，准备对“Dick This is another test”执行相同操作（如果需要）。停止录制。

问题是在录制宏之后（如上所述），当我随后执行宏时，无论我从哪个单元格开始（例如 B3 或工作表其他位置的完全空白单元格），它都不会将此人的姓名留在 B 列，并将其余信息放在 C 列，而是将“Tom”放在 B 列，将“This is a test.”放在 C 列。

我意识到每次它都会回到 B2 并处理“汤姆”的信息……但为什么呢？（我认为这就是使用相对引用应该处理的事情。）

我还意识到我可以转到数据，将文本分列，然后以这种方式将姓名后面的剩余单词分列到单独的列中……但是当说要用空格键分隔列时，它会将每个单词放入单独的列中。我不想这样做。我希望该人的名字保留在起始列中，而该人名字后面的所有内容都放入右侧的下一列中。

我做错了什么？我该如何解决？我宁愿从宏记录过程执行此操作，而不是使用 VBA……但如果没有其他方法，我会使用 VBA……尽管我需要解释适用的 VBA 步骤是什么。

谢谢你的帮助！

答案1

这是执行此操作的另一种方法，使用 Excel VBA 方法，同时证明宏记录器记录的内容通常不是完成某件事的最佳方法。

在此处输入图片描述

Option Explicit
Sub SplitOnFirstSpace()
    'declare variables
    Dim rg As Range, ws As Worksheet
    Dim c As Range

'set range and worksheet variables
Set ws = ThisWorkbook.Worksheets("sheet2") 'or whatever
With ws
    Set rg = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With

'Split on first space only
For Each c In rg.Rows

    'check if any spaces so as not to repeat the split if already done
    If InStr(c, " ") > 0 Then _
        c.Resize(columnsize:=2) = Split(c, " ", 2)
Next c

End Sub

在此处输入图片描述

答案2

首先，感谢您详细说明您所尝试过的内容。

一般来说你需要替换这个：

Range("B2")

然后使用单词“Selection”进行适当的调整，引用该单词而不是 B2。您可以使用 .Offset(row,col) 来指定相对于 Range 对象的相对位置。Selection 是一个 Range 对象，因此您可以使用它来表示“右侧”或“下一行”。

如果您在代码块中将代码包含在您的帖子中，我们可以查看它，但这应该有效：

Public Sub SplitOnFirstSpace()

Dim both_parts As String
Dim first_part As String
Dim the_rest As String

'This macro should only work on one cell

If Selection.Rows.Count > 1 Or Selection.Columns.Count > 1 Then 'more than one row or column selected

    'display a message then exit the subroutine
    MsgBox "More than one row or column selected." & vbCrLf & _
            Selection.AddressLocal, vbCritical, "Split on first space"

    Exit Sub

End If

' get the value in the selected cell
both_parts = Selection

If InStr(1, both_parts, " ") = 0 Then 'there were no spaces in the selected cell

    ' do nothing

Else

    'Get the text to the left of the first space:

    'LEFT(string,length) gets the first 'length' characters from 'string'
    'InStr(start,String1,String2) gets the first position of 'String2' within 'String1'
    ' (after starting the search at position 'start')
    first_part = Left(both_parts, InStr(1, both_parts, " "))

    'Get everything after the first space:

    'Mid(string,start,length) gets a substring of length 'length'
    ' from string 'string', starting at position 'start'
    'Len() gets the length of a string
    the_rest = Mid(both_parts, Len(first_part) + 1, Len(both_parts))

    'put the first part in the selected cell
    Selection = first_part

    'put the rest in the cell to the right of the selected cell

    'Range.Offset(rowoffset,columnoffset) lets you refer to ranges relative to a specific range
    ' in this case, the offset is relative to the selected cell
    Selection.Offset(0, 1) = the_rest

End If

' move down to the next row
Selection.Offset(1, 0).Select

End Sub

我编辑了该函数以指定仅在选择一个单元格时才应使用此宏，而在选择没有空格的单元格时则不执行任何操作。

作为参考，使用的功能：

信息结构函数在另一个字符串中搜索一个字符串的位置。

左边函数返回字符串开头的有限数量的字符。

中函数返回字符串中任意位置的子字符串。

抵消Range 对象的属性，用于引用相对于给定范围的范围。

答案3

谢谢您的回答...但仍然难以让它发挥作用。

我将你们发送的代码粘贴到 VBA 编辑器中（当然，一次粘贴一个），关闭 VBA 编辑器，然后尝试运行它，但对我来说都不起作用。（每次都听到“Windows 10 默认蜂鸣声”。）现在我刚开始使用 VBA，所以如果我需要做与此相关的其他事情，请提供建议。

同时，这是我录制宏生成的 VBA 代码。再次重申，其背后的思维过程是... F2 编辑单元格，Hom​​e 转到第一个字符，Ctrl+RightArrow 跳过姓名，按住 Shift 并按 End 选择其余条目，Ctrl+X 剪切，Tab 移动到下一列，Ctrl+V 粘贴 [即，除人名之外的所有内容]，Enter 向下移动一行并向左移动一列：

Sub Macro8() ' ' Macro8 宏 ' ' 键盘快捷键：Ctrl+Shift+M ' ActiveCell.Select ActiveCell.FormulaR1C1 = "Tom " ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, -1).Range("A1").Select End Sub

正如我所指出的，当我录制宏时，从 B2 开始，也就是我最初有“汤姆，这是一个测试。”的地方，它（根据需要）在 B2 中“留下”“汤姆”，并将“这是一个测试。”放入 C2。

但是当我随后从任何单元格执行宏（使用我分配的键盘快捷键 Ctrl+Shift+M）时...例如 B3 最初有“Dick 这是另一个测试。” 它会将“Tom”（而不是“Dick”）放在 B3 中，并将“这是一个测试。”（而不是“这是另一个测试。”）放入 C3...也就是说，最初在 B2 中的确切信息（但在“Tom”之后拆分）...而不是按照我的预期执行，即获取最初在 B3 中的内容（“Dick 这是另一个测试。”）并“保留” B3 中的“Dick”，并将“这是另一个测试。”放入 C3。

类似地，如果我从工作表上的任何空白单元格（例如 C14）执行它，它会将“Tom”放入 C14，将“这是一个测试。”放入 D14。

我可以在我的代码中看到“Tom”是其中的一部分（如预期的那样……至少对于第一次执行而言）……但我没有看到“这是一个测试。”即使它确实将其放入右侧的下一个单元格中。为什么这不会显示在代码中？

所以现在看来​​我实际上有两个问题：（1）我原来的问题，关于我的代码有什么问题以及我需要做什么来修复它；（2）我将你推荐的代码粘贴到 VBA 编辑器中可能做错了什么，它们对我来说不起作用（当我试图运行它们时我只听到“Windows 10 默认蜂鸣声”）？

有趣的是，我将这张 Excel 工作表转换为 Word 表，并且能够在 Word 中创建宏来执行此任务......但这只是绕了个远路，并不能帮助我提高 Excel 熟练程度！

再次感谢您的任何想法和帮助。

相关内容