Solved

Automated way of removing line breaks within a cell in Excel

Posted on 2014-03-24
3
200 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 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

932 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

11 Experts available now in Live!

Get 1:1 Help Now