[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Automated way of removing line breaks within a cell in Excel

Posted on 2014-03-24
3
Medium Priority
?
216 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 34

Accepted Solution

by:
Rob Henson earned 2000 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 34

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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

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.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

591 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