将列中的文本切成60个字符块 [英] Chop text in column to 60 charactersblocks

问题描述

我有一个工作表，其中有数千行，只有一列(A). A列中的单元格可以为空，最多可以包含1000个字符.我需要运行一个宏，该宏将遍历A列并将其复制到B列.如果有任何具有任何文本> 60字符的单元格，以将其切成60的块，放入下一列.

I have a worksheet that has thousands of rows and only one column (A). The cells in column A can be null or up to and over 1000 characters. I need to run a macro that will loop through column A copying it to column B. If there are any cells that have any text > 60 characters to cut it into blocks of 60 into the next columns.

我有将文本分成60块的代码，但是我不知道如何复制60下的内容，如果为null则移动到下一行，或者循环遍历行.

I have code that breaks text into blocks of 60 but I don't know how to get it to copy anything under 60, move to next row if null or loop through rows.

Sub x()
    Dim cLength As Long, cLoop As Long
    cLength = 60

    For cLoop = 1 To (Len([A2]) \ cLength) + 1
        [A2].Offset(, cLoop).Value = Mid([A2], ((cLoop - 1) * cLength) + 1, cLength)
    Next
End Sub

推荐答案

最快的处理方式！ (不使用循环.一次性处理整个列)

这使用内置的Data | Text To Columns.我们正在使用Fixed Width拆分数据.下面的代码将处理长度不超过1320个字符的字符串.

This uses the inbuilt Data | Text To Columns. We are using Fixed Width to split the data. The below code will handle strings up to 1320 characters in length.

Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    ws.Columns(1).TextToColumns _
        Destination:=Range("A1"), _
        DataType:=xlFixedWidth, _
        FieldInfo:=Array( _
                        Array(0, 1), Array(60, 1), Array(120, 1), Array(180, 1), _
                        Array(240, 1), Array(300, 1), Array(360, 1), Array(420, 1), _
                        Array(480, 1), Array(540, 1), Array(600, 1), Array(660, 1), _
                        Array(720, 1), Array(780, 1), Array(840, 1), Array(900, 1), _
                        Array(960, 1), Array(1020, 1), Array(1080, 1), Array(1140, 1), _
                        Array(1200, 1), Array(1260, 1), Array(1320, 1) _
                         ), _
        TrailingMinusNumbers:=True
End Sub

如果您要手动执行操作，那么您将执行此操作.

If you were to do it manually then you would be doing this.

这篇关于将列中的文本切成60个字符块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！