When you join a number with text using the CONCATENATE function, the number often loses its formatting. However, you can concatenate a number without losing its format by using the JOIN function in Google Sheets.
For example, suppose you’ve entered $2,500.55 in cell A1. Now, take a look at the following CONCATENATE formula:
=CONCATENATE("Pay an amount of ", A1)The result of this formula will be: “Pay an amount of 2500.55”
As you can see, the number has lost its currency and thousand-separator formatting. So, how can we preserve the formatting in the output? Here’s the solution.
Solution 1: Using the JOIN Function
Use the JOIN function to concatenate text and numbers without losing the number’s formatting. Here’s the formula:
=JOIN("", "Pay an amount of ", A1)Solution 2: Using the CONCATENATE Function with the TEXT Function
If you prefer using the CONCATENATE function, you can format the number within the formula by wrapping it in the TEXT function. I’ll explain this in detail below.
Concatenate Text with Numbers Without Losing Their Format
Here are some example formulas. The core difference between the formulas is the number format specified in the TEXT function. Let’s explore a few scenarios.
Example 1: Concatenate a Number Keeping Thousand Separators
In this example, we have first names, last names, and amounts in the range A2:C4. The goal is to concatenate them into a sentence, preserving the thousand separators.
Use the following formula in cell E2, then drag it down:
=CONCATENATE("Pay ", TEXT(C2, "#,##0.00"), " to ", A2, " ", B2)Result for the first row: “Pay 2,550.00 to Alison Glover”
Here, the TEXT function ensures the number is formatted as #,##0.00.
Alternatively, you can use the JOIN function, which retains the original formatting:
=JOIN("", "Pay ", C2, " to ", A2, " ", B2)Example 2: Concatenate a Number Retaining the Currency Symbol and Thousand Separators
If the amount column contains currency-formatted numbers, adjust the TEXT function to include the currency symbol. For instance:
=CONCATENATE("Pay ", TEXT(C2, "$#,##0.00"), " to ", A2, " ", B2)This formula returns: “Pay $1,250.25 to Alison Glover”
For the JOIN function, no adjustments are needed, as it automatically retains the cell’s formatting:
=JOIN("", "Pay ", C2, " to ", A2, " ", B2)Example 3: Concatenate a Number Retaining Percentage Formatting
If the amount column contains percentages, modify the TEXT function as follows:
=CONCATENATE("Pay ", TEXT(C2, "0.00%"), " profit to ", A2, " ", B2)This formula will return: “Pay 25.00% profit to Alison Glover”
The equivalent JOIN formula is:
=JOIN("", "Pay ", C2, " profit to ", A2, " ", B2)Wrap-Up
From the examples above, it’s clear that JOIN is a more versatile formula. Unlike CONCATENATE, it doesn’t require additional formatting for numbers, making it a more flexible solution. On the other hand, CONCATENATE requires the use of the TEXT function to apply custom formatting based on the cell value.
The key difference lies in how the two functions handle formatting. While the CONCATENATE function enforces formatting using the TEXT function, the JOIN function preserves the original formatting of the cell.
This distinction is significant. For instance, if a cell contains 25%, the JOIN function will return 25%, maintaining the cell’s formatting. In contrast, the CONCATENATE and TEXT combination will return the value based on the specific number format you define in the TEXT function.
Resources
- Difference Between JOIN, TEXTJOIN, CONCATENATE Functions in Google Sheets
- Convert Currency-Formatted Text to Numbers in Google Sheets
- How to Format Numbers as Fractions in Google Sheets
- Format Numbers as Currency Using Formulas in Google Sheets
- How to Sort Numbers Formatted as Text in Google Sheets (Formula Approach)
- Format Numbers to Millions and Thousands in Google Sheets
