Transpose data

If you have a raw data like below and you want to add it to tables or columns h to k



Here is the code-


Option Compare Text

Sub test()
Dim i As Long
j = 1
For i = 1 To Range("a65356").End(xlUp).Row

If Left(Cells(i, 1).Value, 5) = "Name:" Then

j = j + 1

Range("h" & j).Value = Right(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 6)

ElseIf Left(Cells(i, 1).Value, 8) = "Company:" Then

Range("i" & j).Value = Right(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 9)

ElseIf Left(Cells(i, 1).Value, 8) = "Address:" Then

Range("j" & j).Value = Right(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 9)

ElseIf Left(Cells(i, 1).Value, 8) = "Website:" Then

Range("k" & j).Value = Right(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 9)
End If
Next i
End Sub

Excel Macro File - http://www.filefactory.com/file/cc85354/n/transpose.xlsm