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 number in a single cell. Are you having trouble combining text and number 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!


Download Practice Workbook

You can download the Excel workbook from here.


4 Suitable Ways to Combine Text and Number in Excel

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. Use of 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 texts and their salaries as numbers.

Suitable Ways to Combine Text and Number in Excel

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.

Suitable Ways to Combine Text and Number in Excel

  • 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.

Read More: Combine Text and Formula in Excel (4 Simple Ways)


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.

Suitable Ways to Combine Text and Number in Excel

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.
=B5&" "&TEXT(C5,"00%")
  • Second, press Enter.
  • As a consequence, you will be able to combine text and numbers as shown below.

Suitable Ways to Combine Text and Number in Excel

  • 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.

Suitable Ways to Combine Text and Number in Excel

  • 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.

Suitable Ways to Combine Text and Number in Excel

  • 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.

Read More: How to Add Text to Cell Value in Excel (4 Easy Ways)


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.

Suitable Ways to Combine Text and Number in Excel

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.

Suitable Ways to Combine Text and Number in Excel

  • 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.

Suitable Ways to Combine Text and Number in Excel

  • 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.

Suitable Ways to Combine Text and Number in Excel

  • 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.


Similar Readings


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 names of some persons as texts and their salaries as numbers.

Suitable Ways to Combine Text and Number in Excel

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.

Suitable Ways to Combine Text and Number in Excel

  • 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 number. 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.

Suitable Ways to Combine Text and Number in Excel

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.

Suitable Ways to Combine Text and Number in Excel

  • 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.

Suitable Ways to Combine Text and Number in Excel

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

Suitable Ways to Combine Text and Number in Excel

  • 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.

Suitable Ways to Combine Text and Number in Excel

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

Suitable Ways to Combine Text and Number in Excel

  • 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.

Suitable Ways to Combine Text and Number in Excel

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

Suitable Ways to Combine Text and Number in Excel

  • 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.

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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo