Numbers in Excel can be many different formats such as the fraction, the percentages, numbers with decimals and negative numbers etc. In this post, we are going to discuss how to keep decimals in numbers when merging with the text.
1. Number of Decimals
When a number merges the text directly, all the decimals of the number will stay.
To keep the number with 2 decimals, and keep "0" when the number is less than 1, please use the formula below.
=TEXT(B2,"0.##")&" "&A2
=TEXT(B2,"0.00")&" "&A2
Where "0.##" is to keep 0 when the number is less than 1. For example, 0.16 will display as 0.16.
Please refer to the formulas below to decide the number of decimals to keep:
TEXT(B2,"0"): No decimals;
TEXT(B2,"0.0"): to keep 1 decimal;
TEXT(B2,"0.00"): to keep 2 decimals;
TEXT(B2,"0.000"): to keep 3 decimals
TEXT(B2,"0.0000"): to keep 4 decimals.
……
To display without 0 when the number is less than 1, please change "0.##" to "#.##", where 0.16 will display as ".16".
=TEXT(B2,"#.##")&" "&A2
=TEXT(B2,"#.00")&" "&A2
TEXT(B2,"#"): No decimals;
TEXT(B2,"#.#"): to keep 1 decimal;
TEXT(B2,"#.##"): to keep 2 decimals;
TEXT(B2,"#.###"): to keep 3 decimals
TEXT(B2,"#.####"): to keep 4 decimals.
……
2. RoundUp and RoundDown
In some cases, except for the number of decimals, you also need strategies on how to keep the decimals. The above to formulas use the regular rounding strategies. When the nearest digit is equal to or greater than 5, the number is to round up, while it is less than 5, the number is to round down.
If you need all numbers to round up and keep 2 digits when merging with the text, please use the formula below:
=ROUNDUP(B2,2)&" "&A2
If you need all numbers to round down and keep 2 digits when merging with the text, please use the formula below:
=ROUNDDOWN(B2,2)&" "&A2
If you need to keep 3 decimals, please change 2 to 3, or any other numbers for the number of decimals you want to keep.