Need a quick way to parse the contents of a cell

[bfreescott]

I have a cell with thousands of values and I need to parse the values into separate cells after every third value.
Text to columns only wants to separate every value, but I need to keep the values in groups of three.

 

[Special-K99]

Example data?

 

[bfreescott]

Excel 2013/2016
	A
1	111 90 95 103 111 112 111 112 112 100 112 155 163 158 158 155 152 149 148 148 148 148 150 150 150 150 149 149 149 149 148 148 147 144 143 142 141 138 136 134 132 131 131 130 129 128 127 127 126 124 124 124 124 124 124 125 124 124 123 124 125 125 125 124 123 122 121 121 121 120 120 120 119 118 118 118 119 120 119 119 119 119 120 121 122 123 124 124 126 127 128 130 131 131 134 135 137 138 138 140 143 147 150 151 153 155 157 159 160 161 161 162 162 161 159 157 156 155 156 154 150 154 158 148 124 107 103 107 108 113 113 110 112 118 127 136 141 140 139 139 141 143 144 144 144 144 144 143 143 144 144 144 144 145 145 145 145 145 145 145 145 145 145 146 146 146 146 145 145 145 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 144 143 143 143 142 142 142 144 144 144 144 143 143 143 142 142 142 142 143 143 143 143 143 143 143 143 143 143 142 141 141 141 141 141 141 141 141 141
Report

 

[Special-K99]

Bit too much data there methinks.

Still not sure what you're after.

There's some tools here so you can provide an image of a small amount of data input and output

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[GR00007]

Hopefully this may meet your need:
In A2 place and copy down to A238

=MID(A1,FIND(" ",A1) +1,999)

In B1 place and copy to B238

=IFERROR(LEFT(A2,FIND(" ",A2) -1),A2)

 

[DanteAmor]

Put the data as shown in the following example.

In cell B1 enter the number 4, in C1 the 7, select B1 and C1, drag to the right to fill row1, you can go through the numbering to the number 1000 or 2000.
In C2, put the formula and drag it to the right. If you have more records down, then copy the formulas down.


<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:438px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Texto</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">4</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">7</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">10</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">13</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">16</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">19</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >111 92 93 201 202 203 441 442 443 551 552 553 601 602 603 701 702 703</td><td >111 92 93</td><td >201 202 203</td><td >441 442 443</td><td >551 552 553</td><td >601 602 603</td><td >701 702 703</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=TRIM(MID(SUBSTITUTE(" " & SUBSTITUTE(" " & $A2," ",REPT(" ",1000),B$1)," ",REPT(" ",1000),B$1-2),1000,999))</td></tr></table></td></tr></table> <br /><br />

The other option is with a macro. Warn if you have more records in column A

 

[DanteAmor]

I also put a macro.

Sub Parse_Contents()
    Dim c As Range, m As Variant, i As Long, j As Long, cad As String
    
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
        j = 2
        m = Split(c, " ")
        For i = 0 To UBound(m) Step 3
            If i + 2 > UBound(m) Then
                If i + 1 > UBound(m) Then
                    cad = m(i) '& " " & m(i + 1) & " " & m(i + 2)
                Else
                    cad = m(i) & " " & m(i + 1) '& " " & m(i + 2)
                End If
            Else
                cad = m(i) & " " & m(i + 1) & " " & m(i + 2)
            End If
            Cells(c.Row, j).Value = cad
            j = j + 1
        Next
    Next
    MsgBox "Done"
End Sub

 

[bfreescott]

I also put a macro.

Sub Parse_Contents()
    Dim c As Range, m As Variant, i As Long, j As Long, cad As String
    
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
        j = 2
        m = Split(c, " ")
        For i = 0 To UBound(m) Step 3
            If i + 2 > UBound(m) Then
                If i + 1 > UBound(m) Then
                    cad = m(i) '& " " & m(i + 1) & " " & m(i + 2)
                Else
                    cad = m(i) & " " & m(i + 1) '& " " & m(i + 2)
                End If
            Else
                cad = m(i) & " " & m(i + 1) & " " & m(i + 2)
            End If
            Cells(c.Row, j).Value = cad
            j = j + 1
        Next
    Next
    MsgBox "Done"
End Sub

Awesome! Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[veyselemre]

I don't speak English, I've removed the checks. I'm sorry.

Sub Parse_Contents()    Dim c As Range, m As Variant, i As Long, j As Long, cad As String, md As Byte
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
        j = 2
        m = Split(c, " ")
        md = (UBound(m) + 1) Mod 3
        If md > 0 Then ReDim Preserve m(0 To UBound(m) + (3 - md))
        For i = 0 To UBound(m) Step 3
            cad = m(i) & " " & m(i + 1) & " " & m(i + 2)
            Cells(c.Row, j).Value = Trim(cad)
            j = j + 1
        Next
    Next
    MsgBox "Done"
End Sub