14 total posts
Can you give more information?
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.
I just want certain text on its own line within the cell
I just want the equivalent of using 'ALT-Enter' within the cell -- to move certain text onto its own line within the cell.
Re: replacing space with return inside cells
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.
7 years late but here is your answer
You used the wrong ascii code do a find and replace with ascii Alt 0010
Try the SUBSTITUTE function
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.
That's a smart character handling function indeed.
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
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.
That's do-able too, without necessarily jumping into code
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#
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.
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.
Swisse frightened me he was so good,
He just stopped posting suddenly which is a shame, so I too welcome another VBA-code expert.
Mark, a small correction.
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.
I missed that.
That's OK. It's good to have another Excel proficient member around, VBA expert or not.
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.
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.