+ Reply to Thread
Results 1 to 15 of 15

Splitting multiple lines of data within a single cell into multiple rows?

  1. #1
    Registered User
    Join Date
    08-10-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2010
    Posts
    7

    Splitting multiple lines of data within a single cell into multiple rows?

    Hi, I am new to the board, and an Excel neophyte.

    I've just been assigned the task at work of re-doing a spreadsheet so that multiple sets of number codes contained within a single cell can be placed on multiple rows. The spreadsheet was sent to us from another company. But we have to place the number codes (from columns A and M) into our own spreadsheet into single multiple rows. I've been doing this manually (copy/paste) for two days, but thought there just has to be an easier way. I have to do this for 9 workbooks.

    The spreadsheets are in Excel 2010.

    The first workbook I'm working on has 298 rows of data.

    Rows 1 to 8 contain a header. Row 9 contains the column titles and is frozen from scrolling.

    The data I need to split or separate are from columns A and M only. They occur always on the same row (there is other data in columns B through L which needs to be kept on the same rows when split, but can just be repeated).

    Not every cell in columns A and M contains multiple lines of number codes that I need to split. But many do, and they are random. And when they occur, they always are on the same row in both column A and column M. Sometimes the amount of lines from column A and M don't match, but this is an error from the company who sent them to us.

    Example:

    Column A, (row 12)
    QT01176-I-GIO-B-PFD-003/
    QT01176-I-GIO-B-PFD-004/
    QT01176-I-GIO-B-PFD-005/
    QT01176-I-GIO-B-PFD-006/
    QT01176-I-GIO-B-PFD-007/
    QT01176-I-GIO-B-PFD-008/
    QT01176-I-GIO-B-PFD-009

    Column M (row 12)
    MCF-1-942-00-1598-01
    MCF-1-942-00-1599-01
    MCF-1-942-00-1600-01
    MCF-1-942-00-1601-01
    MCF-1-942-00-1602-01
    MCF-1-942-00-1603-01
    MCF-1-942-00-1604-01


    A17
    BIC01176-I-GIO-DR-01
    BIC01176-I-GIO-DR-02
    BIC01176-I-GIO-DR-03
    BIC01176-I-GIO-DR-04
    BIC01176-I-GIO-DR-05
    BIC01176-I-GIO-DR-06
    BIC01176-I-GIO-DR-07

    M17
    MCF-1-951-00-0722-01
    MCF-1-951-00-0723-01
    MCF-1-951-00-0724-01
    MCF-1-951-00-0725-01
    MCF-1-951-00-0726-01
    MCF-1-951-00-0727-01
    MCF-1-951-00-0728-01

    A30
    QT01176-I-GIO-G-DR-021/
    QT01176-I-GIO-G-DR-022/
    QT01176-I-GIO-G-DR-023/
    QT01176-I-GIO-G-DR-024/
    QT01176-I-GIO-G-DR-025/
    QT01176-I-GIO-G-DR-026/
    QT01176-I-GIO-G-DR-027/
    QT01176-I-GIO-G-DR-028/
    QT01176-I-GIO-G-DR-029/
    QT01176-I-GIO-G-DR-030/
    QT01176-I-GIO-G-DR-031/
    QT01176-I-GIO-G-DR-032/
    QT01176-I-GIO-G-DR-033/
    QT01176-I-GIO-G-DR-034/
    QT01176-I-GIO-G-DR-035/
    QT01176-I-GIO-G-DR-036/
    QT01176-I-GIO-G-DR-037/
    QT01176-I-GIO-G-DR-038/
    QT01176-I-GIO-G-DR-039/
    QT01176-I-GIO-G-DR-040/
    QT01176-I-GIO-G-DR-041/
    QT01176-I-GIO-G-DR-042/
    QT01176-I-GIO-G-DR-043/
    QT01176-I-GIO-G-DR-044/
    QT01176-I-GIO-G-DR-045/
    QT01176-I-GIO-G-DR-046/
    QT01176-I-GIO-G-DR-047/
    QT01176-I-GIO-G-DR-048/
    QT01176-I-GIO-G-DR-049

    M30
    MCF-1-977-00-0127-01
    MCF-1-977-00-0128-01
    MCF-1-977-00-0129-01
    MCF-1-977-00-0130-01
    MCF-1-977-00-0131-01
    MCF-1-977-00-0132-01
    MCF-1-977-00-0133-01
    MCF-1-977-00-0134-01
    MCF-1-977-00-0135-01
    MCF-1-977-00-0136-01
    MCF-1-977-00-0137-01
    MCF-1-977-00-0138-01
    MCF-1-977-00-0139-01
    MCF-1-977-00-0140-01
    MCF-1-977-00-0141-01
    MCF-1-977-00-0142-01
    MCF-1-977-00-0143-01
    MCF-1-977-00-0144-01
    MCF-1-977-00-0145-01
    MCF-1-977-00-0146-01
    MCF-1-977-00-0147-01
    MCF-1-977-00-0148-01
    MCF-1-977-00-0149-01
    MCF-1-977-00-0150-01
    MCF-1-977-00-0151-01
    MCF-1-977-00-0152-01
    MCF-1-977-00-0153-01
    MCF-1-977-00-0154-01
    MCF-1-977-00-0155-01

    etc.
    Last edited by PeatMoss; 08-10-2014 at 08:44 PM.

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    Could you be so kind to send a sample of the input data and the expected result in excel?

  3. #3
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    Perhaps you can make use of the Text To Columns command under the Data tab?

  4. #4
    Registered User
    Join Date
    08-10-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    Quote Originally Posted by rcm View Post
    Could you be so kind to send a sample of the input data and the expected result in excel?
    Sure, send where? To whom?

  5. #5
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    You can attach files here on the foum

  6. #6
    Registered User
    Join Date
    08-10-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    Quote Originally Posted by bmouse View Post
    You can attach files here on the forum
    Here is the spreadsheet I am working from:

    Response from WP on VSS Rev 0 (Pad #2 - AB) on 2014-06-11 - Copy.xlsx

    Here is what I'd like the spreadsheet to look like:

    End Look.xlsx

    Note* Some cells in column A are blank (and need to stay this way). Also, some cells with multiple lines of number codes in column A are written side by side e.g. QTO1176-I-GI-J-BOM-004 / QTO1176-I-GI-J-BOM-005 / QTO1176-I-GI-J-BOM-006 rather than on separate lines within the cell. Most of the number codes in column A are on separate lines within the cell though.

    Not sure why the company that sent the spreadsheet to us used / after each number code, but that's the way it came.

    Thanks to anyone with any help.
    Last edited by PeatMoss; 08-11-2014 at 09:14 PM.

  7. #7
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    Best advice I can give is try using Text To Columns under the Data tab to split the cell onto multiple columns first. Use the Fixed width option.

    Afterwards you can use the TRANSPOSE formula to transfer the data from columns to rows.

  8. #8
    Registered User
    Join Date
    08-10-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    Quote Originally Posted by bmouse View Post
    Best advice I can give is try using Text To Columns under the Data tab to split the cell onto multiple columns first. Use the Fixed width option.

    Afterwards you can use the TRANSPOSE formula to transfer the data from columns to rows.
    Not sure what I'm doing wrong but after I use fixed width and click through the 'next' and 'finish' the data just ends up in adjacent cells. Is text to columns supposed to create new columns?

    Could you possibly walk me through it..?

  9. #9
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    In your original post you said that you need to split data from 1 cell to multiple rows. I'm no expert, but I would use Text to Columns first, split that 1 cell into multiple columns, then use the TRANSPOSE formula to transfer the data from columns to rows, job done.

    Ok, lets assume that you have data in A1. Select A1, click Text to Columns, select Fixed Width and Finish. Lets assume that you got 10 columns of data from the cell A1, so your data range now is A1:A10. For the TRANSPOSE formula to work correctly, you need to now the number of cells for transposing. In this case the number is 10. If you are working with large amounts of data, select the full range of columns and look in the bottom right corner of Excel - there is an Auto Count feature, which counts occupied cells.

    So we have got 10 cells for transposing. Select at least 10 blank cells/rows anywhere on the sheet and type this formula =TRANSPOSE(A1:A10) then press Shift+Ctrl+Enter without exiting the formula. If done correctly, the formula will look like this {=TRANSPOSE(A1:A10)} and your data will be converted from columns to rows.

    Afterwards you can copy it and use Paste Special - Values, to avoid trouble with data loss and stuff because of the formulas involved.

  10. #10
    Registered User
    Join Date
    08-10-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    I selected a cell (i.e. A30) There are 29 lines of number codes in this cell.

    I then clicked 'Text to Columns'

    I selected fixed width

    > next

    Menu Screen:
    this screen lets you set field widths (column breaks)
    To CREATE a break line, click a desired position
    To DELETE a break line, double click on the line
    To MOVE a break line, click and drag it

    Data preview:


    The preview looked fine, so I clicked > next

    Menu screen:
    This screen lets you select each column and set Data Format

    Column data format
    General
    Text
    Date
    Do not import column (skip)

    Data preview:

    I had selected 'General' and the preview looked ok, though not sure what it means.

    Then I selected > finish.

    A message popped up asking 'Do you want to replace the contents of the destination cells?' Ok or Cancel

    If I select cancel, nothing seems to change. I don't see extra columns anywhere. Cell A30 is still visible with all 29 lines of numbers in it.

    If I select OK. There is now one number code in A30 and the other 28 number codes are in all adjacent cells to the right of A30 which spill over into columns N30, O30, P30,Q30, R30, S30, T30, U30, V30. However, only N30 and O30 have single number codes in them. The rest still have multiple lines of numbers in their cells.

    Capture.PNG
    Last edited by PeatMoss; 08-14-2014 at 08:37 PM.

  11. #11
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    Have a look at the example, it should give you a general idea of how to split cells and transpose them. Does it resemble what you're going for?
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-10-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    Quote Originally Posted by bmouse View Post
    Have a look at the example, it should give you a general idea of how to split cells and transpose them. Does it resemble what you're going for?
    Yes, but not with the numbers broken up across columns. Besides, it doesn't do for me what is shown in your attachment.

  13. #13
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    In that case skip the last step. Isn't step 3 your goal?

  14. #14
    Registered User
    Join Date
    08-10-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    What I'm saying is when I do step 2 (text to columns) it doesn't look like what you have in your attachment.

  15. #15
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Splitting multiple lines of data within a single cell into multiple rows?

    I did exactly as shown in the example - copy/paste the values from the original, do text to columns with fixed width and finish.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 02-15-2013, 08:58 AM
  2. Splitting multiple entries in single cell into multiple columns
    By David_Mitchell in forum Excel General
    Replies: 12
    Last Post: 01-24-2013, 06:57 AM
  3. Need help moving multiple lines of data in single cell to unique subsequent rows
    By brettmburns in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-21-2012, 03:04 PM
  4. Replies: 0
    Last Post: 06-29-2012, 03:16 PM
  5. Replies: 7
    Last Post: 08-16-2010, 11:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1