[Solved] How to split the wrap text

[Tam]

Hi, i want to unwrap the text in vba, like for example below, I used the textsplit, it gives what i want, but its too many to do in the sheet, how can i do that in vba for whole sheet, thx

 

[shamimarita]

Hi, i want to unwrap the text in vba, like for example below, I used the textsplit, it gives what i want, but its too many to do in the sheet, how can i do that in vba for whole sheet, thx
View attachment 1584

Hello Tam,

You can use the VBA code to split the text from the wrapped text. The VBA code will loop through each cell containing text in column A. It splits the text in each cell by line breaks.
Each piece of text is then placed into the rows in column B, starting from the same row where the wrapped text was found.

Copy and paste the following VBA code:

Sub SplitWrappedText()
    Dim ws As Worksheet
    Dim cell As Range
    Dim splitValues() As String
    Dim i As Integer
    Dim outputRow As Long

    ' Set the worksheet to the active sheet
    Set ws = ThisWorkbook.ActiveSheet

    ' Loop through each cell in column A that has wrapped text
    For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        If Len(cell.Value) > 0 Then
            ' Split the cell's text by line breaks
            splitValues = Split(cell.Value, Chr(10))
            outputRow = cell.Row
           
            ' Place each split text into column B
            For i = LBound(splitValues) To UBound(splitValues)
                ws.Cells(outputRow, 2).Value = splitValues(i)
                outputRow = outputRow + 1
            Next i
        End If
    Next cell
End Sub

Run the VBA code then the wrapped text in column A will be split and placed into column B, with each piece on a new row, without altering the content of column A.



Download the Excel workbook: