Text to columns Data alignment

Question

I have a bunch of data, for example:

FOB0046WHT White business casual plain classic fit 20.8 41.7 1 2 24 24 24 0 20.8 1 0 12.2 0.584 24.3       
FOB0083BLU Denim blue business casual plain extra slim fit 14.5 58.2 1 4 1 1 1 0 43.6 3 -29.1 6 0.413 24.2 0.416    
FOB0184NPK Navy and pink business casual double face slim fit 16.6 16.6 1 1 0 0 0 0 0 0 16.6 5 0.303 5 0.303   
FOB0186SKY Sky business casual non iron button down classic fit 90.3 90.3 4 4 19 4.8 19 0 0 0 90.3 49.4 0.548 49.4 0.548   
FOB0186WHT White business casual non iron button down classic fit 83.4 83.4 3 3 6 2 6 0 0 0 83.4 73.5 0.882 73.5 0.882   
FOB0188WHT White business casual non iron button down slim fit 104.2 164.6 5 8 33 6.6 33 0 60.4 3 43.7 53.2 0.51 80.5 0.489   
FOB0209SKY Sky non iron extra slim fit business casual dobby grid shirt 60.4 102.1 3 5 24 8 24 0 41.7 2 18.8 33.6 0.556 57.4 0.562 
FOB0046CHA Charcoal classic fit business casual shirt 145.8 187.5 7 9 20 2.9 20 0 41.7 2 104.2 83.5 0.573 107.4 0.573      
FOB0046CHM Chambray denim classic fit business casual shirt 20.8 20.8 1 1 0 0 0 0 0 0 20.8 11.8 0.568 11.8 0.568     
FOB0046IDG Indigo classic fit business casual shirt 20.8 20.8 1 1 0 0 0 0 0 0 20.8 11.8 0.566 11.8 0.566      
FOB0083CHA Charcoal extra slim fit business casual shirt 20.8 20.8 1 1 0 0 0 0 0 0 20.8 12.5 0.601 12.5 0.601     
FOB0083CHM Chambray denim extra slim fit business casual shirt 57.7 57.7 3 3 0 0 0 0 0 0 57.7 32.7 0.566 32.7 0.566    
FOB0083IDG Indigo extra slim fit business casual shirt 104.2 104.2 5 5 21 4.2 21 0 0 0 104.2 62.3 0.598 62.3 0.598     
FOB0112CHM Chambray denim slim fit business casual shirt 76.4 97.3 4 5 24 6 24 0 20.8 1 55.6 41 0.537 53 0.545     
FOB0186LPK Light pink non iron classic fit bus cas button down shirt 62.5 62.5 3 3 20 6.7 20 0 0 0 62.5 32.9 0.527 32.9 0.527 

I use text to column, but due to the text being a different number of words, the numbers go all out of alignment, is there any way of getting them back in their corresponding columns?

Answer 1

With data like this in column A:

Running this short macro:

Sub ReFormatData()
    Dim N As Long, i As Long, st As String
    Dim j As Long
    N = 15

    For i = 1 To N
        st = Cells(i, 1).Text
        ary = Split(st, " ")
        Cells(i, 2).Value = ary(0)
        For j = 2 To UBound(ary)
            If IsNumeric(ary(j)) Then Exit For
            Cells(i, 3).Value = Cells(i, 3).Value & " " & ary(j)
        Next j

        L = 4
        For k = j To UBound(ary)
            Cells(i, L).Value = ary(k)
            L = L + 1
        Next k
    Next i
End Sub

will produce this in columns B and beyond:

Answer 2

If you prefer non VBA solution using a large formula try the following:
=RIGHT(RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),LEN(RIGHT(A2,LEN(A2)-FIND(" ",A2,1)))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(A2,LEN(A2)-FIND(" ",A2,1))&"0123456789"))+1)

It will find the first place of the number after the first blank space (after the first word which contain 46 FOB0046WHT) and the result will be all the numbers in one cell, you can then use Text to Columns after copying the result and Paste special Values.

Answer 3

Here is a solution with regular expressions. To use it you will need RegEx Find/Replace Add-in.

• formula for code:
  =RegExFind(A1,"[A-Z0-9]*")
• formula for description:
  =RegExReplace(A1,"[A-Z0-9]* (.*[a-z]) [0-9. ]*","$1")
• formula for the numbers:
  =RegExFind(SUBSTITUTE($A1,$B1&" "&$C1&" ",""),"[0-9.-]* ",COLUMN()-3)

It also works correctly if text contains numbers, keeps everything until the last letter in the description.