How to get text from wrapped text show on different columns using VBA

Question

I have a vba script that copies and paste data from one workbook to the other. The data that is being pasted over are wrapped text. I need my vba script to take the wrapped text and turn it to what you see below.

Below is the data that is being copied that has the wrapped text.

Below is what I want it to look like when pasting to the new workbook.

Below is my script of what I have right now.

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    Application.ScreenUpdating = False
    Cells.WrapText = False
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Worksheets("MyFile").Range("A1:A352:I1:I352").copy _

        Workbooks("Book1").Worksheets("Sheet1").Range("A1:A352:I1:I352").PasteSpecial xlPasteValues
        'OpenBook.Close False
    End If
    Application.ScreenUpdating = True
End Sub

Sorry for some reason. I forgot to add that one. I'll edit it — alphanumEric, May 14 '22 at 02:41
Is there data is A1 and in A2 and in A3, etc.? If so how do want it distributed? — Excel Hero, May 14 '22 at 02:42
Yes, thats the range. Not all of the data has wrapped text in them. The wrap text is only in one row. My code is just copying more rows and the wrap text is in row D. — alphanumEric, May 14 '22 at 02:43
The data is just in row D2. I did range("A1:I352") because the I wanted to copy from rows A - I, but the wrapped text is only in one row, which is row D, which starts on D2. so the data is going from D2:D300. in that row. — alphanumEric, May 14 '22 at 02:48
You mean column D, right? But if the data are in more than one consecutive cell vertically, I'm not seeing how it can be split up like you want because all of the values would collide. — Excel Hero, May 14 '22 at 02:52
Sorry, yea I meant columns. So, in the worksheet there is data going from range A1:A350 all the way to I1:I350 and in columns D2:D350 there's wrapped text as above. I want to take that copy the wrap text then paste this into a new workbook, but get the output for the second image as above. — alphanumEric, May 14 '22 at 03:01
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/244733/discussion-between-alphanumeric-and-excel-hero). — alphanumEric, May 14 '22 at 04:01

score 2 · Accepted Answer · answered May 14 '22 at 02:59

2

Perhaps this can get you going...

Sub alphanumEric()
    Dim i&, s, v
    
    s = OpenBook.Worksheets("MyFile").Range("D2").Value2
    s = Split(s, vbLf)
    ReDim v(0 To UBound(s), 1 To 1)
    
    For i = 0 To UBound(s)
        v(i, 1) = s(i)
    Next
    Workbooks("Book1").Worksheets("Sheet1").Range("D2").Resize(UBound(s) + 1).Value2 = v
    
End Sub

answered May 14 '22 at 02:59

Excel Hero

14,253
4
33
40

You can actually assign the split array directly to the worksheet range even though it's one-dimensional:) – Paul Kelly May 14 '22 at 03:32
1

I try to avoid `WorksheetFunction.Transpose()` for a variety of reasons. – Excel Hero May 14 '22 at 03:37
For certain applications it's fine but of course depends on requirements. – Paul Kelly May 14 '22 at 04:14
It's really not. – Excel Hero May 14 '22 at 05:01

score 1 · Answer 2 · answered May 14 '22 at 03:00

This will covert the cell text into 4 cells if each character is separated by line feed. Note this example copies from cell A1 to B1.

Dim arr As Variant, rowCount As Long
' Split the text to an array by line feed
arr = Split(Sheet1.Range("A1").Value, vbLf)
rowCount = UBound(arr) - LBound(arr) + 1

' Copy to the new cells
Sheet1.Range("B1").Resize(rowCount, 1) = WorksheetFunction.Transpose(arr)

How to get text from wrapped text show on different columns using VBA

2 Answers2