使用VBA在Excel中将字符串分成不同的单元格

问题描述

例如，我有一个名为str的字符串变量.该str的值为:

For example, I have a string variable named str. This str has a value of:

apple
orange
pineapple

每个单词由newVbLine分隔.我想将其移动到单元格上.A1包含 apple ，A2包含 orange ，A3包含菠萝.

Each word is separated by a newVbLine. I want to move it on cells. A1 contains apple, A2 contains orange and A3 contains pineapple.

谢谢.

推荐答案

下面的代码将A列中由 vbNewLine 分隔的字符串拆分为B列.

The code below splits strings delimited by vbNewLine in column A into column B.

请更改
ws1.[b1] .Resize(UBound(X)-LBound(X)+ 1，1)= Application.Transpose(X)
到
ws1.[a1] .Resize(UBound(X)-LBound(X)+ 1，1)= Application.Transpose(X)

如果您想覆盖A列

Sub Spliced()
    Dim ws1 As Worksheet
    Dim X
    Set ws1 = Sheets(1)
    X = Split(Join(Application.Transpose(ws1.Range(ws1.[a1], ws1.Cells(Rows.Count, "A").End(xlUp))), vbNewLine), vbNewLine)
    ws1.[b1].Resize(UBound(X) - LBound(X) + 1, 1) = Application.Transpose(X)
End Sub

这篇关于使用VBA在Excel中将字符串分成不同的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！