Solved

Automated way of removing line breaks within a cell in Excel

Posted on 2014-03-24
3
203 Views
Last Modified: 2014-03-24
I have inherited a large spreadsheet which contains a significant number of line breaks (using ALT + ENTER) within the cells of several columns of text. I am looking for a macro or automated way to remove all line breaks within cells so that the text wraps properly. I couldn't find anything in the "Find & replace" feature to address this...

Thanks!
Andrea
0
Comment
Question by:Andreamary
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39951003
You can do with formulas in another column:

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

The ASCII code for "Carriage Return" is 10 so this will substitute all CRs with "". Might be worth changing to " " (Space) instead otherwise last word on one line will be concatenated with first word on the next.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39951006
Or using Find and Replace:

In the find box press Alt and type 010
In the replace type a space

Thanks
Rob H
0
 

Author Closing Comment

by:Andreamary
ID: 39951198
Perfect. And I followed your advice on building in the " " (space). Thanks for your quick response...

Andrea
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

789 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