When you join a number with text by using the CONCATENATE function, the number loses its formatting. But you can concatenate a number without losing its format by using the TEXT function in Google Sheets.
Suppose I have entered $2,500.55 in cell A1. Now see the below CONCATENATE formula.
=CONCATENATE("Pay an amount of ",A1)
The result of this formula would be like “Pay an amount of 2500.55”. Here, as you can see, the number has lost its currency and thousand separator formatting. So what is the solution to this? How can one preserve the formatting in the CONCATENATE formula?
Google Sheets Formula to Concatenate a Number without Losing Its Format
To retain the number format in the CONCATENATE formula, as I have already mentioned, you should use the TEXT function. There are different custom formats that you can use in the TEXT function. But I am only picking the most useful ones.
Most Useful Custom Number Formats in Google Sheets Text Function that We Can Use in Concatenate
First, see the custom number formats that you may be required in Concantenate. Then we can move to the tips on how to concatenate a number without losing its format. There are a total of five number formats that I am going to use.
1. Thousand Separators Format
#,##0.00
1,250.00
2. Thousand Separators, Negative and Positive Numbers Format
#,##0.00_);(#,##0.00)
Positive: 1,250.00
Negative: (1,250.00)
3. Currency Symbol and Thousand Separators Format
$#,##0.00
$1,250.00
4. Currency Symbol, Thousand Separators, Negative and Positive Numbers Format
$#,##0.00_);($#,##0.00)
Positive: $1,250.00
Negative: ($1,250.00)
5. Percentage Format
0.00%
25.00%
Now let’s see how to concatenate a number without losing its formatting in Google Sheets.
The Formula to Concatenate a Number Retaining Format
Here are the example formulas. Actually, all the formulas are the same except the number formats used in the TEXT function. The number formats in the TEXT function is as per the above five formats.
I] Concatenate a Number Keeping the Thousand Separators
See the formula in E2 which is then dragged into E3 and E4.
=CONCATENATE("Pay ",TEXT(C2,"#,##0.00")," to ",A2," ",B2)
You may want to know how the above formula would look like without the number formatting. It would be like this.
=CONCATENATE("Pay ",C2," to ",A2," ",B2)
The formatting is applied to the value in the cell C2.
II] Concatenate a Number Keeping the Thousand Separators, -ve and +ve Number Formatting
This is another example of how to concatenate a number without losing its format in Google Sheets. You should only pay attention to the Text function in this formula.
=CONCATENATE("Pay ",TEXT(C2,"#,##0.00_);(#,##0.00)")," to ",A2," ",B2)
If you see the result in Cell E2 you can clearly understand one thing. The negative number is formatted not as per the original format in cell C2. Why so?
The formatted number is within a bracket and which is a standard. If you want the same format that in Cell C2, just use the formula under the example I above.
III] Concatenate a Number Retaining Currency Symbol and Thousand Separators
This formula is similar to the formula under example I. The only difference is the Dollar sign used in the number format in the TEXT function.
=CONCATENATE("Pay ",TEXT(C2,"$#,##0.00")," to ",A2," ",B2)
IV] Concatenate a Number Retaining the Currency Symbol, Thousand Separators, -ve and +ve Number Formatting.
This formula is as per formula under the point II. The difference here again the use of the Dollar symbol.
=CONCATENATE("Pay ",TEXT(C2,"$#,##0.00_);($#,##0.00)")," to ",A2," ",B2)
V] Concatenate a Number Retaining the Percentage
Here is the final formula in this tutorial. It’s simple to retain the percentage in Concatenate.
=CONCATENATE("Pay ",TEXT(C2,"0.00%")," profit to ",A2," ",B2)
That’s all. You can use the above formatting options to concatenate a number without losing its format in Google Sheets. Enjoy!
