How to insert newline in you excel formulae

To add a newline or a hard return in front of certain text, we try the Find & Replace option, but ends up nowhere. Instead make use of the CHAR(10), MS has provided, in your formulaes.

Usage:

=CONCATENATE(“Heading1”,CHAR(10),”Content under Heading1″)

 

Let me take a complex example also, to get you the feel of the power of CHAR(10):

Before Text:

a. Open Firefox b. Enter the URL as http://www.google.com c. Click on enter

After Text:

a. Open Firefox

b. Enter the URL as http://www.google.com

c. Click on enter

Solution:

=CONCATENATE(IFERROR(MID(C4,SEARCH(“a. “,C4),SEARCH(“b. “,C4)-SEARCH(“a. “,C4)),””),CHAR(10),IFERROR(MID(C4,SEARCH(“b. “,C4),SEARCH(“c. “,C4)-SEARCH(“b. “,C4)),””),CHAR(10),IFERROR(MID(C4,SEARCH(“c. “,C4),LEN(C4)),””))