使用VBA标识单元格中的第二行文本

I have a bunch of cells that have two lines of text in each cell after pulling the data into Excel. What I am looking to do is use a macro with the Trim function to remove everything in the cell after the second line. I'm puzzled with this data, it's as if you were to enter data in a cell and Enter down to the next cell, but it's one cell and is not merged.

在将数据拉入Excel后,我有一堆单元格,每个单元格中有两行文本。我要做的是使用带有Trim功能的宏来删除第二行之后的单元格中的所有内容。我对这些数据感到困惑,就好像你要在一个单元格中输入数据并输入到下一个单元格,但它是一个单元格并且没有合并。

ex.

Someone's Name [123] Procedure room, procedure done

某人的姓名[123]手术室,手术完成

Is there a way to identify this line break?

有没有办法确定这个换行符?

thanks so much for any assistance, my heads spinning and I'm punching out for the day. cheers

非常感谢任何帮助,我的头脑旋转,我正在为这一天打气。干杯

1 个解决方案

#1

Just look for the ASCII-10. Select the cells you wish to process and run:

只需寻找ASCII-10。选择要处理和运行的单元格:

Sub KleanUp()
    Dim r As Range
    For Each r In Selection
        r.Value = Split(r.Value, Chr(10))(0)
    Next r
End Sub

Only the first line will be retained in each cell.

每个单元格中仅保留第一行。

EDIT#1:

As Ralph points out, ASCII-13 should also be considered, therefore:

正如拉尔夫指出的那样,也应考虑ASCII-13,因此:

Sub KleanUp2()
    Dim r As Range
    For Each r In Selection
        r.Value = Split(Replace(r.Value, Chr(13), Chr(10)), Chr(10))(0)
    Next r
End Sub

This converts to a single-style line-break.

这将转换为单一样式的换行符。

EDIT#2:

It is possible to improve the performance of the sub by:

可以通过以下方式改善子的性能:

reading the data into a VBA array as a single large block

将数据作为单个大块读取到VBA阵列中

looping through the array (in VBA)

循环遍历数组(在VBA中)

transferring the data back to the worksheet in a single block

将数据传输回单个块中的工作表

#1