Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-12-22
3
Medium Priority
?
2,125 Views
Last Modified: 2013-11-29
Hi

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 http://www.techonthenet.com/excel/questions/remove_chars.php   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



0
Comment
Question by:Jay_Ratansi
3 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 26110684
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
 
 
Kelvin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 26110686
Try looking for a line feed OR a line feed/carriage return combination:

=SUBSTITUTE(A2,CHAR(10),"")

=SUBSTITUTE(A2,CHAR(13)&CHAR(10),"")

Kevin
0
 

Author Closing Comment

by:Jay_Ratansi
ID: 31669330
Thanks, this worked perfectly.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 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