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'.

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.


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.


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 -
This works! Thanks
by MM121 / August 14, 2015 6:01 AM PDT

This works! Thanks

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.


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


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.


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)

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 -
by Kees Bakker / March 31, 2006 7:53 PM PST


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.


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.


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.


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.


Collapse -
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 -
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.


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
Computer Help 51,912 discussions
Computer Newbies 10,498 discussions
Laptops 20,411 discussions
Security 30,882 discussions
TVs & Home Theaters 21,253 discussions
Windows 10 1,672 discussions
Phones 16,494 discussions
Windows 7 7,855 discussions
Networking & Wireless 15,504 discussions


The most beautiful phone ever has one wildly annoying issue

The Samsung Galaxy S8's fast speeds and fantastic curved screen make it a top phone for 2017, but the annoying fingerprint reader could sour your experience.