How to Combine Text and Number in Excel (4 Suitable Ways)

Sometimes, we have to represent a text and a number in a single cell in Microsoft Excel. This is mostly used in Schools, Shops, Business Companies, Government Institutions, etc. In order to do so, we need to combine text and numbers in a single cell. Are you having trouble combining text and numbers in Excel? This tutorial will help you to learn how to combine text and number in Excel in 4 suitable ways. Let’s get started!

In this article, we will discuss 4 suitable ways to combine text and number in Excel. The first method is by using the Excel formula with an ampersand operator. We will discuss how to combine text and number in a single cell without formatting and with formatting under this method. The second method is by combining the TEXT function and the CONCATENATE function. The third method is by using the TEXTJOIN function in Office 365 and the last method is by custom formatting the cells.

1. Using Excel Formula with Ampersand Operator

We can use the Excel formula with the Ampersand operator to combine text and number in Excel. We can do this by 2 methods. One is combining text and number without formatting and the other one is combining text and number with formatting. Now we will see examples for both cases below.

1.1 Without Formatting

To combine text and number without formatting, we have taken a dataset like the following figure where we have names of some persons as text and their salaries as numbers.

Now we will combine these texts and numbers in a single cell. In order to do so, follow the steps below.

Steps:

First, select cell D5 and type the following formula:

=B5&" "&C5

Now, press Enter.
As a result, you will see an output like the image below where your text and number will be combined in a single cell.

Second, select cell D5 and drag the Fill Handle to the entire column Combined.
Finally, you will see an output like the below one where all your texts and numbers will be combined in the column Combined without formatting.

1.2 With Formatting

In order to combine text and number with formatting, we have taken a dataset like the following figure where we have different types of texts in the Text column as texts and different types of number formats in the Number column as numbers.

Now we will combine these texts and numbers in a single cell with formatting. In order to do so, follow the steps below.

Steps:

First, you have to enter the following formula with the TEXT function in cell D5 to combine text and numbers.

=B5&" "&TEXT(C5,"00%")

Second, press Enter.
As a consequence, you will be able to combine text and numbers as shown below.

Afterward, you have to enter the following formula in cell D6 to combine text and numbers.

=B6 &TEXT(C6,"Dd-mmm")

Then, press Enter.
Therefore, you will be able to combine text and numbers as shown below.

Furthermore, you have to enter the following formula in cell D7 to combine text and numbers.

=B7&" "&TEXT(C7,"$#,##0")

Then, press Enter.
As a result, you will be able to combine text and numbers as shown below.

Next, you have to enter the following formula in cell D8 to combine text and numbers.

=B8&" "&C8

Then, press Enter.
Therefore, you will be able to combine text and numbers as shown below.

Subsequently, you have to enter the following formula in cell D9 to combine text and numbers.

=B9&" "&TEXT(C9,"d-mmm-yyyy")

Then, press Enter.
Therefore, you will be able to combine text and numbers as shown below.

Furthermore, you have to enter the following formula in cell D10 to combine text and numbers. We are using the TODAY function here.

="Today is " & TEXT(TODAY(),"mm-dd-yyy")

Then, press Enter.
Therefore, you will be able to combine text and numbers as shown below.

🔎 How Does the Formula Work?

B5&” “&TEXT(C5,”0.00%”)

In this formula, the TEXT(C5,”0.00%”) function converts a value to a specified number format and the format is 0.00% which indicates the percentage. Here, B5 represents a string of text. Then, the Ampersand operator joins the string of texts. Moreover, “ “ is the White Space character in between words.

B6 &TEXT(C6,”Dd-mmm”)

In this formula, the TEXT(C6,”Dd-mmm”) function converts a date to a specified date format and the date format is “Dd-mmm”. Here, B6 represents a string of text. Then, the Ampersand operator joins the string of texts. Moreover, “ “ is the White Space character in between words.

B7&” “&TEXT(C7,”$#,##0”)

In this formula, the TEXT(C7,”$#,##0″) function converts a value to a specified number format and the format is “$#,##0” which indicates the currency format in the dollar. Here, B7 represents a string of text. Then, the Ampersand operator joins the string of texts. Moreover, “ “ is the White Space character in between words.

B8&” “&C8

Here, B7 and B9 represent a string of texts. The Ampersand operator joins the string of texts. Moreover, “ “ is the White Space character in between words.

B9&” “&TEXT(C9,”d-mmm-yyyy”)

In this formula, the TEXT(C9,”d-mmm-yyyy”) function converts a date to a specified date format and the date format is “d-mmm-yyyy”. Here, B9 represents a string of text. Then, the Ampersand operator joins the string of texts. Moreover, “ “ is the White Space character in between words.

B10 & ” “& TEXT(TODAY(),”mm-dd-yyy”)

In this formula, the TEXT(TODAY(),”mm-dd-yyy”) function converts a date to a specified date format and the date format is “mm-dd-yyy”. Here, B10 represents a string of text. Then, the Ampersand operator joins the string of texts. Moreover, “ “ is the White Space character in between words.

2. Combining TEXT and CONCATENATE Functions

We can also combine text and number by combining TEXT and CONCATENATE functions, we have taken the same dataset as the following figure where we have different types of texts in the Text column as texts and different types of number formats in the Number column as numbers.

Now we will combine these texts and numbers in a single cell with formatting. In order to do so, follow the steps below.

Steps:

First, you have to enter the following formula in cell D5 to combine text and numbers.

=CONCATENATE(B5," ",TEXT(C5,"00%"))

Second, press Enter.
As a result, you will be able to combine text and numbers as shown below.

After that, you have to enter the following formula in cell D6 to combine text and numbers.

=CONCATENATE(B6," ",TEXT(C6,"Dd-mmm"))

Then, press Enter.
Therefore, you will be able to combine text and numbers as shown below.

Afterward, you have to enter the following formula in cell D7 to combine text and numbers.

=CONCATENATE(B7," ",TEXT(C7,"$#,##0"))

Then, press Enter.
As a consequence, you will be able to combine text and numbers as shown below.

Next, you have to enter the following formula in cell D8 to combine text and numbers.

=CONCATENATE(B8," ",C8)

Then, press Enter.
Therefore, you will be able to combine text and numbers as shown below.

Furthermore, you have to enter the following formula in cell D9 to combine text and numbers.

=CONCATENATE(B9," ",TEXT(C9,"d-mmm-yyyy"))

Then, press Enter.
As a consequence, you will be able to combine text and numbers as shown below.

Next, you have to enter the following formula in cell D10 to combine text and numbers. We are using the TODAY function here.

=CONCATENATE(B10," ",TEXT(TODAY(),"mm-dd-yyy"))

Then, press Enter.
Therefore, you will be able to combine text and numbers as shown below.

🔎 How Does the Formula Work?

CONCATENATE(B5,” “,TEXT(C5,”00%”))

In this formula, the TEXT(C5,”0.00%”) function converts a value to a specified number format and the format is 0.00% which indicates the percentage. Here, B5 represents a string of text. Then, CONCATENATE function combines the texts. Moreover, “ “ is the White Space character in between words.

CONCATENATE(B6,” “,TEXT(C6,”Dd-mmm”))

In this formula, the TEXT(C6,”Dd-mmm”) function converts a date to a specified date format and the date format is “Dd-mmm”. Here, B6 represents a string of text. Then, CONCATENATE function combines the texts. Moreover, “ “ is the White Space character in between words.

CONCATENATE(B7, ” “, TEXT(C7,”$#,##0”))

In this formula, the TEXT(C7,”$#,##0″) function converts a value to a specified number format and the format is “$#,##0” which indicates the currency format in the dollar. Here, B7 represents a string of text. Then, CONCATENATE function combines the texts. Moreover, “ “ is the White Space character in between words.

CONCATENATE(B8, ” “, C8)

Here, B7 and B9 represent a string of texts. Then, CONCATENATE function combines the texts. Moreover, “ “ is the White Space character in between words.

CONCATENATE(B9, ” “,TEXT(C9,”d-mmm-yyyy”))

In this formula, the TEXT(C9,”d-mmm-yyyy”) function converts a date to a specified date format and the date format is“d-mmm-yyyy”. Here, B9 represents a string of text. Then, CONCATENATE function combines the texts. Moreover, “ “ is the White Space character in between words.

CONCATENATE(B10, ” “, TEXT(TODAY(),”mm-dd-yyy”))

In this formula, the TEXT(TODAY(),”mm-dd-yyy”) function converts a date to a specified date format and the date format is“mm-dd-yyy”. Here, B10 represents a string of text. Then, CONCATENATE function combines the texts. Moreover, “ “ is the White Space character in between words.

3. Applying TEXTJOIN Function

If we want to combine text and number by applying the TEXTJOIN function, we have taken a dataset like the following figure where we have the names of some persons as texts and their salaries as numbers.

Now we will combine these texts and numbers in a single cell. In order to do so, follow the steps below.

Steps:

First, select cell D5 and type the following formula:

=TEXTJOIN(" ",TRUE,B5:C5)

Now, press Enter.
As a result, you will see an output like the image below where your text and number will be combined in a single cell.

Second, select cell D5 and drag the Fill Handle to the entire column Combined.
Finally, you will see an output like the below one where all your texts and numbers will be combined in the column Combined without formatting.

4. Using Custom Formatting

We can use Custom Formatting to combine text and numbers. We have taken the previous dataset like the following figure where different types of texts in the Text column and different types of number formats in the Number column are taken.

Now we will combine these texts and numbers in a single cell. In order to do so, follow the steps below.

Steps:

First, copy the range of the cells (C5:C10).
Then, paste it on the cells (D5:D19) as shown below.

Now, select cell D5 and press ‘Ctrl+1’ from the keyboard.
As a result, the Format Cells window will appear.
Then, select Custom from the Category option.
Next, type “John’s salary increased “ before 0% in the Type box.
Then, click on OK.

As a result, you will be able to combine text and numbers as shown below.

Now, select cell D6 and press ‘Ctrl+1’ from the keyboard.
As a result, the Format Cells window will appear.
Then, select Custom from the Category option.
Next, type “Payment is due” before [$-en-US]d-mmm;@ in Type box.
Then, click on OK.

As a result, you will be able to combine text and numbers as shown below.

Afterward, select cell D7 and press ‘Ctrl+1’ from the keyboard.
As a result, the Format Cells window will appear.
Then, select Custom from the Category option.
Next, type “Total sales “ before $#,##0 in Type box.
Then, click on OK.

As a result, you will be able to combine text and numbers as shown below.

Now, select cell D8 and press ‘Ctrl+1’ from the keyboard.
As a result, the Format Cells window will appear.
Then, select Custom from the Category option.
Next, type “Kane’s score is “ before General in the Type box.
Then, click on OK.

As a result, you will be able to combine text and numbers as shown below.

Now, select cell D9 and press ‘Ctrl+1’ from the keyboard.
As a consequence, the Format Cells window will appear.
Then, select Custom from the Category option.
Next, type “Payment completed” before [$-en-US]d-mmm-yyyy;@ in Type box.
Then, click on OK.

Hence, you will be able to combine text and numbers as shown below.

Furthermore, select cell D10 and press ‘Ctrl+1’ from the keyboard.
As a consequence, the Format Cells window will appear.
Then, select Custom from the Category option.
Next, type “Today is “ before m/d/yyyy in the Type box.
Then, click on OK.

As a result, you will be able to combine text and numbers as shown below.

Things to Remember

If you want to combine text and number without formatting, then using the Excel formula with the Ampersand operator and TEXTJOIN function will be a better option for you.
If you have different types of number formats and you want to keep those formats, then you can use the TEXT function, a combination of TEXT and CONCATENATE functions, or custom formatting.

Download Practice Workbook

You can download the Excel workbook from here.

Combine Text and Numbers.xlsx

Conclusion

Hence, follow the above-described methods. Thus, you can easily learn how to combine text and number in Excel. Hope this will be helpful. Don’t forget to drop your comments, suggestions, or queries in the comment section below. Stay tuned and keep learning!

<< Go Back to Concateante Numbers in Excel | Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Combine Text and Number in Excel (4 Suitable Ways)

1. Using Excel Formula with Ampersand Operator

1.1 Without Formatting

1.2 With Formatting

2. Combining TEXT and CONCATENATE Functions

3. Applying TEXTJOIN Function

4. Using Custom Formatting

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply