Excel (2003/2007) : how to remove carriage return from cell

Posted on 2009-12-22
Last Modified: 2013-11-29

I'm extracting data from an accounting program which contains a multi-line field (=memo field in Access). Running an Access query & then export to Excel, I get following:

Excel2003 : the relevant cell shows text separated by square box the text again for line 2

Excel2007: doesn't show square box but displays the second line together with first line (without any space to separate).

In both cases, when exported to CSV, the second line text vanishes.

I've tried the following formula in excel  =SUBSTITUTE(A2,CHAR(13),"") suggested by   but this doesn't resolve ie. when excel saves as csv, the second line content vanishes.

Appreciate help to resolve this either in Access (accounting data table is odbc linked to access)  or excel (access query is exported to excel).

Jay Ratansi

Question by:Jay_Ratansi
    LVL 22

    Expert Comment

    by:Kelvin Sparks
    You need to replace the square box with a Carriage Return. In access this can be the VBA constant vbCrLf
    You could use the Access REPLACE Function as Replace(Fieldsname,CHAR(13),vbCrLf).
    This MIGHT help
    LVL 81

    Accepted Solution

    Try looking for a line feed OR a line feed/carriage return combination:




    Author Closing Comment

    Thanks, this worked perfectly.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now