+ Reply to Thread
Results 1 to 10 of 10

Formula to split a string of text within a cell by the carriage returns

  1. #1
    Registered User
    Join Date
    10-07-2014
    Location
    Norwich, England
    MS-Off Ver
    365
    Posts
    58

    Formula to split a string of text within a cell by the carriage returns

    Hi
    Wondering if anyone has tried something like this before?
    I am trying to 'transform' some transactional data from one ERP system to get it into another.
    One of the columns I'm trying to transform is 'Description'. Unfortunately, on the old system, users were able to use carriage returns and the desciption, although appearing in one field, could be split over multiple rows.
    In my Excel extract from the old system, all the text is captured nicely in one cell. However the carriage returns are there and the text is actually split across multiple lines in a cell.
    My import routine can't handle this and will just cut everything off after the first carriage return (either that or it just errors, I can't remember).
    Does anyone have a clever way of splitting a string of text in one cell by the carriage returns and making each 'line' appear on a separate row?

    I have attached an example workbook with two worksheets. 'Data' shows what I'm dealing with and 'Formula' is what I'm looking for!

    Many thanks in advance
    Tom
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to split a string of text within a cell by the carriage returns

    Hi

    an attempt


    =TRIM(MID(SUBSTITUTE(Data!C$4,CHAR(10),REPT(" ",1000)),1+(ROWS($1:1)*1000-1000),1000))

    please refer to the attachment..
    Attached Files Attached Files
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Formula to split a string of text within a cell by the carriage returns

    This isn't easily done using formula. There are other better alternatives.

    For Excel 2013, you can download PowerQuery add-in from MS.
    https://www.microsoft.com/en-us/down...0-562ea695869f

    Steps:
    1. Select data range. Load data from table/range and go to query edit.
    2. Select Description column. Split Column -> By delimiter.
    3. Use #(lf) as delimiter and split at each occurrence. Go to advanced options and Split into - Rows.
    4. Load back to sheet.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Formula to split a string of text within a cell by the carriage returns

    Bit convoluted, but you could use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to convert the Carriage Returns to a visible character. Then use Text to Columns to split the text. Finally, transpose the columns to rows.

    Or you could go straight to Text to Columns and use Alt-010 as the delimiter (in the "Other" box) Note: MUST use the numeric key pad

    You'd probably need VBA to process multiple rows. Maybe post a sample with a wider range of more realistic data examples and desired results.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    10-07-2014
    Location
    Norwich, England
    MS-Off Ver
    365
    Posts
    58

    Re: Formula to split a string of text within a cell by the carriage returns

    Hi Canapone
    Many thanks for your suggestion. Seems to work nicely! I wonder... can we scale it over some more data?
    In the attached example I've added a few more items to the 'data' worksheet. Should have said, but I'm also hoping to use a formula to populate column B of the 'Formula' worksheet (Item).
    Is that possible?
    Many thanks
    Tom

  6. #6
    Registered User
    Join Date
    10-07-2014
    Location
    Norwich, England
    MS-Off Ver
    365
    Posts
    58

    Re: Formula to split a string of text within a cell by the carriage returns

    Hi CK76
    Thanks for your suggestion. I will try and download the plug in now (although I'm on a work machine so may need an admin password and that could take a while... )
    Will let you know how I get on!
    Cheers
    Tom

  7. #7
    Registered User
    Join Date
    10-07-2014
    Location
    Norwich, England
    MS-Off Ver
    365
    Posts
    58

    Re: Formula to split a string of text within a cell by the carriage returns

    Hi TMS
    Nice idea! I am familiar with text to columns so I should be able to work something out using that!
    I will see whether canapone's suggestion works over a larger data set and if not may go this way!
    Many thanks for your help, most appreciated.
    Thanks
    Tom

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to split a string of text within a cell by the carriage returns

    Hi,

    I'm working on this formula

    =LOOKUP(-1,-SEARCH(C4,Data!$C$4:$C$5),Data!$B$4:$B$10)


    In B4:B10 a serie of big strings.

    So far I was not able to start from 1 again ( this segment ROWS($1:1)), when formula has to read the second, third big string

    Regards

  9. #9
    Registered User
    Join Date
    10-07-2014
    Location
    Norwich, England
    MS-Off Ver
    365
    Posts
    58

    Re: Formula to split a string of text within a cell by the carriage returns

    I'll be honest, we're going well beyond my level of Excel knowledge haha!
    If you manage to crack it then awesome! If not I'll try the plug in CK76 suggested or TMS' suggestion of text to columns and then transposing to rows.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Formula to split a string of text within a cell by the carriage returns

    You're welcome. Thanks for the rep.

+ 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. Outputting to text files to include carriage returns
    By carlmeads1975 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-20-2017, 08:47 AM
  2. Exporting to text file with carriage returns
    By carlmeads1975 in forum Excel General
    Replies: 2
    Last Post: 09-20-2017, 05:29 AM
  3. Replies: 1
    Last Post: 09-04-2015, 04:48 AM
  4. [SOLVED] Referencing text fields with in cell carriage returns
    By cadmanweyland in forum Excel General
    Replies: 4
    Last Post: 04-18-2013, 09:41 PM
  5. [SOLVED] Clearing the carriage returns in a cell with text
    By dwhite30518 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-05-2012, 11:59 AM
  6. Replies: 1
    Last Post: 11-05-2010, 06:09 PM
  7. Replies: 3
    Last Post: 11-17-2005, 03:10 PM

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