Add a Line Break in Cell Contents Using Excel Formula

Written by kazamraza no comments
Classified in : Excel Formulas, Text Functions Tags : none

Inserting a line break in Excel cell contents helps us, when

  • We are writing very long formulas
  • The length of content exceeding cell/column width

We can insert line break by two methods.

While writing long formulas use Alt + Enter to insert a line break and to add a line break in text string we use CHAR(10).

In the following example I use CHAR(10) and click Wrap Text (Alt + H + W) from Home Tab.
◢ A B C D E
1 Name Address City, Country Formula Result
2 Kazam Raza H#1, Block E, PCSIR Staff Lahore, Pakistan =A2&CHAR(10)&B2&CHAR(10)&C2 Kazam Raza
H#1, Block E, PCSIR Staff
Lahore, Pakistan
3          
4          

While writing long formulas, the best practice is to use Alt + Enter to add a line break, for example, calculate tax on salary

Excel formula without line break, very difficult to understand and modify:


	=(IF(D5<=400000,0,IF(AND(D5>400001,D5<=750000),(D5-400000)*0.05,IF(AND(D5>750001,D5<=1400000),17500+((D5-750000)*0.1),IF(AND(D5>1400001,D5<=1500000),82500+((D5-1400000)*0.125),IF(AND(D5>1500001,D5<=1800000),95000+((D5-1500000)*0.15),IF(AND(D5>1800001,D5<=2500000),140000+((D5-1800000)*0.175),IF(AND(D5>2500001,D5<=3000000),262500+((D5-2500000)*0.2),IF(AND(D5>3000001,D5<=3500000),362500+((D5-3000000)*0.225),IF(AND(D5>3500001,D5<=4000000),475000+((D5-3500000)*0.25),IF(AND(D5>4000001,D5<=7000000),600000+((D5-4000000)*0.275),IF(D5>7000001,1425000+((D5-7000000)*0.3),""))))))))))))

Excel formula with line break, easy to understand and modify:


=(IF(D5<=400000,0,
IF(AND(D5>400001,D5<=750000),(D5-400000)*0.05,
IF(AND(D5>750001,D5<=1400000),17500+((D5-750000)*0.1),
IF(AND(D5>1400001,D5<=1500000),82500+((D5-1400000)*0.125),
IF(AND(D5>1500001,D5<=1800000),95000+((D5-1500000)*0.15),
IF(AND(D5>1800001,D5<=2500000),140000+((D5-1800000)*0.175),
IF(AND(D5>2500001,D5<=3000000),262500+((D5-2500000)*0.2),
IF(AND(D5>3000001,D5<=3500000),362500+((D5-3000000)*0.225),
IF(AND(D5>3500001,D5<=4000000),475000+((D5-3500000)*0.25),
IF(AND(D5>4000001,D5<=7000000),600000+((D5-4000000)*0.275),
IF(D5>7000001,1425000+((D5-7000000)*0.3),""))))))))))))

.