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.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

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.