Add a Line Break in Cell Contents Using Excel Formula
Written by kazamraza
no comments
Classified in : Excel Formulas, Text Functions
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.
- Using Alt + Enter
- Using formula CHAR(10) (CHAR Function)
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),""))))))))))))