How to find and replace with a newline in Excel? [SOLVED]
I'm looking for the set of characters I need to enter in the 'Replace With' section of the 'Find and Replace' window. I've multiple cells in the following format:-
AAAA
BBBB
CCCC
In the 'Find' section, I've entered +++ and now looking for suggestions on what to enter in the 'Replace With' section. I've tried with everything from CHAR(10), CHAR(13), CTRL+j, CTRL+J and \n. Nothing seems to work. Some of the videos on the Internet point out that CTRL+J works on Windows. But I'm using Excel for Mac and equivalent commands for Excel for Mac don't seem to work.
Does anyone know a way to fix this?
- AAAA+++BBBB+++CCCC
AAAA
BBBB
CCCC
In the 'Find' section, I've entered +++ and now looking for suggestions on what to enter in the 'Replace With' section. I've tried with everything from CHAR(10), CHAR(13), CTRL+j, CTRL+J and \n. Nothing seems to work. Some of the videos on the Internet point out that CTRL+J works on Windows. But I'm using Excel for Mac and equivalent commands for Excel for Mac don't seem to work.
Does anyone know a way to fix this?
Replies
-
Kaustubh KatdareUpdate: I think it's just a matter of how patient you are while searching! I found the answer after posting the question. So here's the 'DEFINITIVE' SOLUTION!
There's no direct way to add a newline to your text. You can do so by using formulas; and the "SUBSTITUTE" function in Excel.
Use the Formula:-
=SUBSTITUTE(CELLNUMBER, TEXT TO FIND, REPLACEMENT TEXT)
Example:
You have the following text in cell A2:-
- AAAA+++BBBB+++CCCC
=SUBSTITUTE(A2, "+++", CHAR(13))
That formula tells excel to LOOK FOR TEXT "+++" IN CELL NUMBER A2 AND REPLACE IT WITH CHAR(13)
CHAR(13) is your newline character that enters a new line in your text! 😀
All the best!
You are reading an archived discussion.
Related Posts
The last time I was stuck in traffic, it made me wonder if personal vehicle is really a favorite mode of commute.
Here's a small poll for CEans. What would...
Inspired by the "Song you are listening right now', here's a thread for the book lovers.
Just leave the name of the book and author in replies.
A line or...
I am starting this series on my own.
I will pick a random quote that I love and post it here everyday. 😀
If you like them, shower the love...
MIT researchers have developed a new material than can morph its surface into golf ball-like dimples to reduce air resistance. Golf enthusiasts claim that the dimpled surface of a golf...
Going by the tagline 'designed for active learning and active students', the new gadget called the Panasonic 3E is a convertible device launched in collaboration with Intel Education and Microsoft....