Split text from one cell into columns

[oaky72]

Hello,

I have a lot of text in 1 cell that i need to split and move to other columns on the same row.

Using the example below, i need to take any text after the : and place it into separate columns. so NATHAN would be in 1 column, Age 6 in the next etc etc. Any help would be great. Thanks. Kevin

Childs Name/s: NATHAN Age: Age 6 Date of Party: 21st January 2017 Party Venue Address : JDR Karting - Gloucester, Unit 5, Madleaze Industrial Estate, Bristol Road, Gloucester GL1 5SG Start Time : 16.05 End Time : 18.00 RSVP Contact & by date: 077143333 (Sophie) Extra Comments (Max 15 words): Dress comfortably and warmly. The karts are suitable for children between 4-7 years old. How did you find us?: Google Would you like a proof? (Important): Yes. Email a proof copy to approve.

<colgroup><col></colgroup><tbody>
</tbody>

 

[Gerald Higgins]

Hi, welcome to the board.

Take a look at the built in Data, Text to Columns function, using the ":" character as the de-limiter.
Should be ideal for this situation.

 

[Fennek]

Hi,

untested:

The text is in A1:

Tx = split(cells(1,1), ":")
j = 1
for i = 1 to ubound(Tx) Step 2
     j = j +1
     cells(1, j) = Tx(i)
next i

regards

 

[oaky72]

Hi, welcome to the board.

Take a look at the built in Data, Text to Columns function, using the ":" character as the de-limiter.
Should be ideal for this situation.

Hi Graham,

Thanks but that only work for the 1st part of the text (childs name) in the cell and not the other parts.

Regards.

Kevin

 

[Gerald Higgins]

OK Kenny , try Fennek's suggestion.

 

[oaky72]

Hi,

untested:

The text is in A1:

Tx = split(cells(1,1), ":")
j = 1
for i = 1 to ubound(Tx) Step 2
     j = j +1
     cells(1, j) = Tx(i)
next i

regards

Hi Fennek,

Apologies as im a bit of a novice. Where do i place this code?

Thanks.

Kevin

 

[Eric W]

As a bit of clarification, do you want the results in row 1 or row 2?

	B	C	D	E
1	NATHAN	Age 6	21st January 2017	etc.
2	Childs Name/s: NATHON	Age: Age 6	Date of Party: 21st January 2017	etc.

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Will there be multiple rows you want to split (NATHAN, MARY, JOHN, etc.)? Will the number of line items in each cell be the same? Would you prefer a macro that you have to initiate via a dialog box or button, or a function that you could place in B1, C1, D1, etc. that would look like:
=GetItem(A1,1)
=GetItem(A1,2)
=GetItem(A1,3)
etc?

 

[oaky72]

As a bit of clarification, do you want the results in row 1 or row 2?

	B	C	D	E
1	NATHAN	Age 6	21st January 2017	etc.
2	Childs Name/s: NATHON	Age: Age 6	Date of Party: 21st January 2017	etc.

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Will there be multiple rows you want to split (NATHAN, MARY, JOHN, etc.)? Will the number of line items in each cell be the same? Would you prefer a macro that you have to initiate via a dialog box or button, or a function that you could place in B1, C1, D1, etc. that would look like:
=GetItem(A1,1)
=GetItem(A1,2)
=GetItem(A1,3)
etc?

Hi Eric. Thanks for the reply. I would like it to look like option 1. There should be no more than about 10 line items for each record but they will vary . Not all will be 10. Some maybe 6 etc. Happy with either a macro box or function.

Thanks. Kevin.

 

[Eric W]

OK, try this:

1) Open a copy of your workbook
2) Right click on the sheet tab on the bottom and select View Code
3) From the menu, select Insert > Module
4) Paste this code in the window that opens:

Sub SplitRecs()
Dim r As Long, c As Long, x As Variant, y As Variant, w As String, i As Long


    On Error GoTo Oops:
    For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        c = 2
        x = Split(Cells(r, "A"), Chr(10))
        For i = 0 To UBound(x)
            y = Split(x(i), ":")
            w = y(1)
            Cells(r, c + i) = "'" & w
        Next i
    Next r
    
    Exit Sub
    
Oops:
    c = c - 1
    w = Cells(r, c + i) & " " & y(0)
    Resume Next
        
End Sub

5) This code looks at column A, and put the results in B,C,D, etc. Change the values in red if that is not the case.
6) Close the VBA editor with Alt-Q.
7) In Excel, press Alt-F8, select SplitRecs, and click Run.

The way this works is by splitting the data in the cell based on line feeds within the cell to get each individual line item. It then splits each item by the : to get just the second part. In your example, the venue address has a line feed in it, which pretty much breaks the model. In this case, I found a way to add that piece to the previous section, but be aware that other line feeds in the data could cause problems.

Let me know how this works.

 

[Rick Rothstein]

Hello,

I have a lot of text in 1 cell that i need to split and move to other columns on the same row.

Using the example below, i need to take any text after the : and place it into separate columns. so NATHAN would be in 1 column, Age 6 in the next etc etc. Any help would be great. Thanks. Kevin

Childs Name/s: NATHAN Age: Age 6 Date of Party: 21st January 2017 Party Venue Address : JDR Karting - Gloucester, Unit 5, Madleaze Industrial Estate, Bristol Road, Gloucester GL1 5SG Start Time : 16.05 End Time : 18.00 RSVP Contact & by date: 077143333 (Sophie) Extra Comments (Max 15 words): Dress comfortably and warmly. The karts are suitable for children between 4-7 years old. How did you find us?: Google Would you like a proof? (Important): Yes. Email a proof copy to approve.

<colgroup><col></colgroup><tbody>
</tbody>

Give this formula a try...

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(":"&$A1,CHAR(10),":"),":",REPT(" ",999)),2*COLUMNS($B:B)*999,999))