Thread: VBA transposing lines into rows but keep formating

I have a list of isbn numbers for books, with each cell being a different book, but some cells have multiple isbn's. I found/replaced all the spaces and commas with carriage returns, so the isbns each have their own lines.
I found some code, below, that splits the cells contents at the CR, and copies it to the B column, but some of the isbn's start with "0" and when it transposes the isbn over it formats it as a number, removing the 0 up front. How do I transpose the multi-line cells to a new column, split by rows, but most importantly keeping the numbers as text formating to leave the "0"?


Sub Lines2Rows()
Dim rList As Range, rCell As Range
Dim lRow As Long, vLines As Variant

Set rList = Range("A2", Range("A" & Rows.Count).End(xlUp)) ' list
lRow = 2
For Each rCell In rList
If rCell <> "" Then
vLines = Split(rCell, vbLf)
Range("B" & lRow).Resize(1 + UBound(vLines)).Value = Application.Transpose(vLines)
lRow = lRow + UBound(vLines) + 1
End If
Next rCell
End Sub



*or is there a short macro that I could use that would find all strings of numbers that arent 10 characters long and add 0's at the beginning to make it to 10 char?