Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Adding Hard Returns in Excel with Find & Replace

Mar 30, 2006 1:18AM 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

- Collapse -
Can you give more information?
Mar 30, 2006 3:52AM 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
Mar 30, 2006 4:06AM 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
Mar 30, 2006 4:23AM 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
Sep 18, 2013 5:32AM PDT

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

- Collapse -
This works! Thanks
Aug 14, 2015 6:01AM PDT

This works! Thanks

- Collapse -
Try the SUBSTITUTE function
Mar 30, 2006 5:02AM 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.
Mar 30, 2006 6:37PM 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
Mar 30, 2006 10:25PM 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.
Mar 31, 2006 7:53PM 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
Apr 1, 2006 2:23AM PST

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.
Apr 1, 2006 4:49PM PST

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!!!
Apr 2, 2006 4:20AM 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
Apr 5, 2006 1:24AM 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!
Oct 13, 2014 2:09AM 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.