In Mac Excel, how can I replace with newline? Ex: Finds all AAA BBB and replace all with

Please any help would be highly appreciated.

  • 1
    what is newline ? are you trying to move data currently entered in coulombs to rows
    – Ruskes
    Oct 4, 2018 at 0:23
  • @Buscar웃 in a single cell, i need to split it with line break
    – A J
    Oct 4, 2018 at 0:25
  • something like this support.office.com/en-us/article/…
    – Ruskes
    Oct 4, 2018 at 0:35
  • are the AAA, BBB the same in the sheet
    – Ruskes
    Oct 4, 2018 at 1:11
  • yeah in same sheet, but in many cells in a column
    – A J
    Oct 4, 2018 at 1:16

2 Answers 2


Manually Adding a Line Break

You can do this by pressing Option Enter at the point you want to insert the line break.

It's best that you format the cell(s) as Text to ensure the best results.

enter image description here

Mass Insert Line Break (a.k.a Search/Replace)

You can do this on a mass basis with Search & Replace. As before, it's important to format your cells as text. In the replace field, use the generic formula

  • The text to be replaced in quotes (" ")
  • & as the concat (concatenate) function
  • CHAR(10) as the symbol for newline

Using this methodology, you can "fix" multiple lines of text (like an address). See the animation below for an example:

Search Replace with Line Break

  • Thank you for the answer. But what I need is not on a single cell. In the whole spread sheet, I have to find the existence of all AAA BBB and then replace it with line break value. Hope the problem is clear now. I will edit the question.
    – A J
    Oct 4, 2018 at 0:50
  • In Word you have the option in Find/Replace to replace with a "special character" like a paragraph or newline character. Excel doesn't seem to offer that. Have you tried replacing your string with the "AAA BBB" plus the MS Word string for a new line (^|) character in the middle? Just spitballing here but often the things work across apps in MS Office apps. Oct 4, 2018 at 1:22
  • This question is related and may provide some insight.
    – bjbk
    Oct 4, 2018 at 13:21
  • Brilliant! Never thought to set the cells as text.
    – bjbk
    Oct 5, 2018 at 4:52

You can use the Find & Replace

enter image description here

How to find or replace a line break in Excel for Windows

To replace a line break with a space or any other separator, enter the line break character in the Find what filed by pressing Ctrl + J. This shortcut is the ASCII control code for character 10 (line break, or line feed).

After pressing Ctrl + J, at first sight the Find what box will look empty, but upon a closer look you will notice a tiny flickering dot like in the screenshot below. Enter the replacement character in the Replace with box, e.g. a space character, and click Replace All. Replacing all line breaks on the sheet with spaces

To replace some character with a line break, do the opposite - enter the current character in the Find what box, and the line break (Ctrl + J) in Replace with.

Excellent instructions found here

Finding the equivalent in Excel for Mac.

Since I do not have Excel to verify it here is what I found for you to try.

enter image description here

  • What I exactly want is what to type in "Replace with:"
    – A J
    Oct 4, 2018 at 1:20
  • @AJ see my updated post with instructions link
    – Ruskes
    Oct 4, 2018 at 2:58
  • 3
    This appears to be for the Windows version, no? How about macOS?
    – bjbk
    Oct 4, 2018 at 3:35
  • @bjbk do you have better answer to help the OP, or you just intuitively feel like my answer is wrong.
    – Ruskes
    Oct 4, 2018 at 4:41
  • 1
    I have tested this, although the tutorial @Buscar웃 references is very good, it only works in the Windows version of Excel. For some reason (control + J) does not work in the replace field, neither does replacing with the return character CHAR(10) as in Replace: ="AAA" & CHAR(10) & "BBB" -- Good starting point though!
    – bjbk
    Oct 4, 2018 at 13:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .