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)),””))

Feel free to leave a reply here...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s