Converting Text to Column

[syb]

Hi,
I need to translate a large text with multiple lines in single cell to column which will make data usable.
Sample file is attached for better understanding of my requirement.Text in B2 needs to be separated as shown (C2 to M2)

Thanking in advance

 

[bosco_yip]

1] Your data in the example is inconsistent, it must tell the computer to split data according to a constant rule.

2] I make a little adjusting to your original B2 data to cell C2 by :

2.1 Removed empty line in between line of Size and Equipment name.

2.2 Combined 2 lines into 1 line in the line of Equipment tag number

2.3 Combined 3 lines into 1 line in the line of Equipment manufacturer

3] then, in C3 enter formula and copy across :

=TRIM(MID(SUBSTITUTE($C2,CHAR(10),REPT(" ",500)),COLUMN(A1)*500-499,500))

Regards
Bosco

 

[jindon]

This should do.

Sub test()
    Dim m As Object, n As Long
    With CreateObject("VBScript.RegExp")
        .Global = True: .MultiLine = True
        .Pattern = "^(\S.+)([\r\n]+\s{5,}(\S[^\r\n]+))*"
        For Each m In .Execute([b2])
            n = n + 1: Cells(2, n + 2) = Join(Array(m.submatches(0), m.submatches(2)))
        Next
    End With
End Sub

 

[syb]

thanks to all