在excel（vba）中分割字符串 [英] Split strings in excel (vba)

问题描述

我正在使用此代码（来自其他用户）来查找列b1中的每个单元格，并找到包含;的单元格。像你好再见一样。代码将拆分单元格在;并将再见直接放在你好下面在一个全新的行..

I am currently using this code(from a fellow user here) to find every cell in column b1 and to find the ones that contain a ";" something like "hello;goodbye". The code will split the cell at the ";" and place "goodbye" directly beneath "hello;" on an entirely new row..

我现在需要的是...如果一个单元格包含 多个 ; （即你好，再见;嗨）它会分裂在每一个;

What I need now is this... if a cell contains multiple ";" (ie "hello;goodbye;yo;hi;hey") it will split at EACH ";" not just the first and then move each to a new row directly beneath the other...

我需要做些什么改变？

Dim r1 As Range, r2 As Range
Dim saItem() As String


For Each r1 In ActiveSheet.Range("B1", Cells(Application.Rows.Count, 2).End(xlUp))
If InStr(1, r1.Value2, ";") > 0 Then
saItem = Split(r1.Value2, ";")
r1 = Trim$(saItem(0)) & ";"
r1.Offset(1).EntireRow.Insert (xlDown)
r1.Offset(1) = Trim$(saItem(1))
End If
Next r1

推荐答案

我在

http://www.excelforum.com/excel-programming/802602-vba-macro-to-split-cells-at-every.html

这是我给出的解决方案：

This is the solution I was given:

Sub tgr()

Dim rindex As Long
Dim saItem() As String

For rindex = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
    If InStr(Cells(rindex, "B").Value, ";") > 0 Then
        saItem = Split(Cells(rindex, "B").Value, ";")
        Rows(rindex + 1 & ":" & rindex + UBound(saItem)).Insert
        Cells(rindex, "B").Resize(UBound(saItem) + 1).Value =     WorksheetFunction.Transpose(saItem)
    End If
Next rindex

End Sub

这篇关于在excel（vba）中分割字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！