How to Concatenate a Number without Losing Its Format in Google Sheets

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)

Concatenate a Number Keeping the Thousand Separators

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)

Number Keeping the Thousand Separators, -ve and +ve Number

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)

Number Retaining Currency Symbol and Thousand Separators

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)

Number Retaining the Currency Symbol, Thousand Separators, -ve and +ve Number

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)

Number Retaining the Percentage

That’s all. You can use the above formatting options to concatenate a number without losing its format in Google Sheets. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.