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

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

CodePudding user response：

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

CodePudding user response：

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)