Office & Productivity Software forum

General discussion

Adding Hard Returns in Excel with Find & Replace

by leojbramble / March 30, 2006 1:18 AM PST

How can I use the 'find and replace' function to replace a space with a hard return in Excel? I've tried typing 'ALT+0013' (the hard return code) into the 'replace with' box, but (predictably) that just replaces the space with the actual TEXT 'ALT+013'.

Post a reply
Discussion is locked
You are posting a reply to: Adding Hard Returns in Excel with Find & Replace
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Adding Hard Returns in Excel with Find & Replace
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Can you give more information?
by MarkFlax Forum moderator / March 30, 2006 3:52 AM PST

It would be helpful if you could give an idea what you are trying to achieve.

Including a hard return in Excel is very difficult, (I know of no way), because of the way Excel is designed into cells. A hard return will take the cursor to the next cell.

So, perhaps there is a different way to achieve what you need.

Mark

Collapse -
I just want certain text on its own line within the cell
by leojbramble / March 30, 2006 4:06 AM PST

I just want the equivalent of using 'ALT-Enter' within the cell -- to move certain text onto its own line within the cell.

Collapse -
Re: replacing space with return inside cells
by Kees Bakker / March 30, 2006 4:23 AM PST

Several ways, in fact, depending on several factors.

1. If the spreadsheet contains no formulas (only data) import (or copy/paste) into a table in MS Word. Use Words replace (that handles special characters) and transform the result to a spreadsheet again.
2. New column with a smart character handling formula (complexity depends on max number of spaces to replace in a cell). Then copy special/paste values over the old one.
3. Simple VBA-routine to loop through the column and do the replace.
4. Manually, but that ain't your question. Jut a last resort.

Kees

Collapse -
7 years late but here is your answer
by vincehelpsyou / September 18, 2013 5:32 AM PDT

You used the wrong ascii code do a find and replace with ascii Alt 0010

Collapse -
Try the SUBSTITUTE function
by richardp123 / March 30, 2006 5:02 AM PST

Let's say your text is in cell A1, and column B is empty.

In cell B1, enter the following formula:

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

That'll replace all spaces in the text of A1 with line breaks. You'll need to ensure that "Wrap Text" is switched on for column B to see the effect (Format Cells > Alignment > Wrap Text) - and maybe set the row height to AutoFit, also.

cheers
Richard

Collapse -
That's a smart character handling function indeed.
by Kees Bakker / March 30, 2006 6:37 PM PST

But, maybe the poster wants "the quick brown fox jumps over the lazy river" not shown as

the
quick
brown
fox
jumps
over
the
lazy
river

but as

the quick brown
fox jumps over
the lazy river

That would need VBA-code to accomplish, in my opinion. But for the simple case substitute is far better than using instring and substrings. Always learning from other members here.

Kees

Collapse -
That's do-able too, without necessarily jumping into code
by richardp123 / March 30, 2006 10:25 PM PST

Split sentence every third word for a maximum of three lines assuming 9 word sentences? OK...

A modification of the formula followed by a wrapping of a similar formula would achieve that:

=SUBSTITUTE(SUBSTITUTE(A1, " ", CHAR(10),3)," ",CHAR(10),5)http://reviews.cnet.com/5224-6129-0.html?forumID=29&threadID=166726&messageID=1846923#
Submit

Naturally, for parametric/more sophisticated manipulation, VBA offers greater control and flexibility ... but for straightforward everyday stuff, it's surprising the amount that can be achieved using built-in Excel functions, without needing to resort to macros.

In my opinion the chief advantage is avoiding the horrible 'Enable Macros' / 'Disable Macros' choice you get when opening a spreadsheet containing VBA objects.

- Richard

Collapse -
Agreed.
by Kees Bakker / March 31, 2006 7:53 PM PST

Richard,

Some time ago a member called swisse, published working and tested VBA-code in this forum to solve practically every Excel-problem posted. Yours is a very welcome alternative view.
Swisse suddenly stopped contributing. Hope you stay around for a while.


Kees

Collapse -
I agree
by MarkFlax Forum moderator / April 1, 2006 2:23 AM PST
In reply to: Agreed.

Swisse frightened me he was so good, Happy

He just stopped posting suddenly which is a shame, so I too welcome another VBA-code expert.

Mark

Collapse -
Mark, a small correction.
by Kees Bakker / April 1, 2006 4:49 PM PST
In reply to: I agree

If you reread this thread, you'll see that Richard says to use Excel functions whereever possible, and preferably not VBA-code. So he might be an expert in it (I really don't know), I doubt if he will show it here.

Kees

Collapse -
Ohh yes!!!
by MarkFlax Forum moderator / April 2, 2006 4:20 AM PDT

I missed that.

That's OK. It's good to have another Excel proficient member around, VBA expert or not.

Thanks Kees.

Mark

Collapse -
Thanks
by leojbramble / April 5, 2006 1:24 AM PDT

Thanks for all the feedback. It ended up being easier for me to just go back in and manually put in the hard returns. I'll spend some time playing with the substitute function, but in this particular instance, I think it would have been more effort than it would have been worth.

I was trying to alter text strings that were in 55 consecutive columns. There was a six digit number appearing at the end of each string that I needed to force onto its own line within the cell, just for clarity's sake.

In retrospect, I suppose it would have been quite easy to just remove the last six digits from the string and put them in a separate cell below... well, it's good to be able to have alternative solutions at our disposal.

Collapse -
THANK YOU!
by gb59 / October 13, 2014 2:09 AM PDT

I know this is 8 years after you posted that suggestion, but I hope you see this.

THANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOU

To sum up, I had to convert a PowerPoint presentation to an Excel database to allow querying. Cutting and pasting - even from PPT to Word to Excel - resulted in every line feed becoming a separate cell in the middle of each Excel row.

That was NOT going to work with the queries.

This tip worked perfectly and saved me hours of time.

Popular Forums
icon
Computer Help 47,885 discussions
icon
Computer Newbies 10,322 discussions
icon
iPhones, iPods, & iPads 3,188 discussions
icon
Security 30,333 discussions
icon
TVs & Home Theaters 20,177 discussions
icon
HDTV Picture Setting 1,932 discussions
icon
Phones 15,713 discussions
icon
Windows 7 6,210 discussions
icon
Networking & Wireless 14,510 discussions

Big stars on small screens

Smosh tells CNET what it took to make it big online

Internet sensations Ian Hecox and Anthony Padilla discuss how YouTube has changed and why among all their goals, "real TV" isn't an ambition.