How to Concatenate and Keep Number Format in Excel

CONCATENATE is a function that joins the values of multiple cells into a single string in text format, regardless of the original format of the concatenated cells. In this article, we will demonstrate 4 simple ways to use the CONCATENATE function and keep the number format in Excel.

Consider the dataset below of 10 people and their points, which have three decimal places. Suppose we want to show the points values with only two decimal places along with their names in column D.

If we apply the CONCATENATE function, the cells will be joined but the two decimal points result will not be returned, as in the image below.

Let’s fix it.

Method 1 – Using the Format Cells Option to Keep the Number Format

In this method, first we will use the TEXT function to set the right format, and then we will use the CONCATENATE function to return the result.

Steps:

• Select the range C5:C14.
• Right-click to launch the Context Menu and choose the Format Cells option.

A small dialog box called Format Cells will appear.

• In the Number tab, choose the Custom option from the Category section.
• Keep a copy of the current number format of the Points column.
• Click OK.

• Select cell D5.
• Enter the following formula to change the values to the correct number format.

• Press Enter.

• Select cell E5 and enter the following formula using the CONCATENATE function:

• Press Enter.

• Drag the Fill Handle icon down to copy the formula to cell E14.

The desired result is returned in column E.

Method 2 – Using the Ampersand Symbol to Retain the Number Format

In this process, we’ll use the TEXT function to get the right format, and then the ampersand symbol to return the result.

Steps:

• Select cell D5.
• Enter the following formula to get the correct number format:

• Press Enter.

• Select cell E5 and enter the following formula using the ampersand symbol:

• Press Enter.

• Drag the Fill Handle icon to copy the formula down to cell E14.

The desired results are returned.

Read More: How to Combine Text and Numbers in Excel and Keep Formatting

Method 3 – Using the TEXTJOIN Function to Concatenate and Keep the Number Format

In this approach, we will combine the TEXT and TEXTJOIN functions to concatenate and keep the number format in Excel.

Steps:

• Select cell D5.
• Enter the following formula to get the correct number format:

• Press Enter.

• Select cell E5 and enter the following formula using the TEXTJOIN function:

• Press Enter.

• Drag the Fill Handle icon to copy the formula down to cell E14.

We have concatenated while retaining the number format.

Method 4 – Using the CONCAT Function to Concatenate and Keep the Number Format

In this last case, we’ll apply the TEXT and CONCAT functions together.

Steps:

• Select cell D5.
• Enter the following formula using the TEXT function to get the correct number format:

• Press Enter.

• Select cell E5 and enter the following formula using the CONCAT function:

• Press Enter.

• Drag the Fill Handle icon to copy the formula down to cell E14.

We have successfully concatenated and kept the number format.

Download Practice Workbook

Related Articles