CrazyEngineers
CrazyEngineers

  • How to find and replace with a newline in Excel? [SOLVED]

    Kaustubh Katdare

    Kaustubh Katdare

    @thebigk
    Updated: Oct 26, 2024
    Views: 1.2K
    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
    Which I want to format as

    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?
    0
    Replies
Howdy guest!
Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.
Replies

  • Kaustubh Katdare

    Administrator • Jun 29, 2014

    Update: 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
    Now, go to cell B2 (which would be an empty cell) and enter-

    =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!
    Are you sure? This action cannot be undone.
    Cancel
Home Channels Search Login Register