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>

 

[oaky72]

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.

Thanks.

After running i get an error

runtime error '9':

subscript out of range


when i debug this is highlighted

w = Cells(r, c + i) & " " & y(0)

 

[oaky72]

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.

also to complicate things. Is it possible to make the heading of each column the wording prior to the colon :

eg Childs Name/s (column name) NATHAN (text in row)

 

[Rick Rothstein]

Give this formula a try...

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

Whether the above formula (and I suspect the code that has been suggested so far) will work for you or not depends on if the following is on one or two lines in your cell (when the cell is widened enough to contain the two lines if they are in fact a single line)...

Party Venue Address : JDR Karting - Gloucester, Unit 5, Madleaze Industrial Estate,
Bristol Road, Gloucester GL1 5SG

So, is that a single long line of text or is it actually two line as it showed in Message #1?

 

[oaky72]

Whether the above formula (and I suspect the code that has been suggested so far) will work for you or not depends on if the following is on one or two lines in your cell (when the cell is widened enough to contain the two lines if they are in fact a single line)...

Party Venue Address : JDR Karting - Gloucester, Unit 5, Madleaze Industrial Estate,
Bristol Road, Gloucester GL1 5SG

So, is that a single long line of text or is it actually two line as it showed in Message #1?

HiRick,

It should be one line of text but when the cell is viewed in full it looks like two lines.

Kevin

 

[Rick Rothstein]

HiRick,

It should be one line of text but when the cell is viewed in full it looks like two lines.

If you make it one line, the formula I posted will work (copy it across)... if it has to remain two lines, then I see what may be an unresolvable problem... if the text for that item can be short enough to fit all on one line in some cases, but long enough to require two lines in other cases, then I do not immediately see how a formula will be able to account for that variation.

 

[Eric W]

I suspect the error is due to data in a format I didn't expect. I said earlier that extra line feeds could have a deleterious effect. I can come up with a version that won't error out, but it won't necessarily give the right answers either.

As far as the headings, that's also tricky. You said that each cell could have different line items. So if I created headings based on the first cell, any cells in lower rows could possibly have data that does not correspond to the column header. I could come up with a version that scans the entire table, extracts all the headers, then places the data in the appropriate column, but it would be a bit more complicated. If you'd like that, let me know, and I'll give it a shot. If you could provide some additional samples, that would help, especially the line that the current macro errored on.

@Rick: nice formula!

 

[AlKey]

Or this
Enter in B1 and copy down

[B]=IFERROR(REPLACE(A1,1,FIND(":",A1)+1,""),"")[/B]

 

[oaky72]

If you make it one line, the formula I posted will work (copy it across)... if it has to remain two lines, then I see what may be an unresolvable problem... if the text for that item can be short enough to fit all on one line in some cases, but long enough to require two lines in other cases, then I do not immediately see how a formula will be able to account for that variation.

Actually when i have looked at the worksheet the Module did work. The full address which looks like it was on 2 lines was shown in the new column in full.

I tested this on 4 rows of similar data and it looks to have failed on row 2. The difference with the Row 2 data is that it has 8 parts of data whereas row 1 (example text i provided) has 9 parts.

This is the row 2 data

Who's party is it?: Ruth's Party Venue Address : 63 Tandragee Rd Gilford Craigavon Co.Armagh BT636HP Date of Party: 4th March 2017 Start Time : 8pm RSVP Contact & by date: Ruth: 0754444 Add Photo (optional): 2989113989175467016447332n.jpg.59b83d06b8f1437d441db6abb440a832 Extra Comments (Max 15 words): Don't worry about presents, I would prefer you donated money to Diabetes UK. (a box will be sitting on the night) How did you find us?: Google

<tbody>
</tbody>

 

[oaky72]

Is it possible to attach my excel file on this forum? Thanks.

 

[AlKey]

Is it possible to attach my excel file on this forum? Thanks.

Unfortunately, not on this forum. Try to post it at http://www.excelforum.com/