How to Combine Text and Numbers in Excel and Keep Formatting

If you are looking for special tricks to combine text and numbers and keep formatting in Excel, you’ve come to the right place. There are four ways to combine text and numbers and keep formatting in Excel. This article will discuss every step of these methods to combine text and numbers and keep the formatting. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.


4 Easy Ways to Combine Text and Numbers in Excel and Keep Formatting

In the following section, we will use four effective and tricky methods to combine text and numbers and keep formatting in Excel. We illustrate how to use TEXT, CONCATENATE functions, and Ampersand operator in the first two methods. In the third method, we demonstrate how to use MS Word to combine text and numbers. In the last method, we use Custom Formatting to combine text and numbers. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Using TEXT Function with Ampersand Operator

Here, we demonstrate how to combine text and numbers in Excel and keep the formatting. We will use the TEXT function with the Ampersand operator. For demonstration purposes, we take a dataset containing the Text and Number column. Now we are going to combine text and numbers in Excel. Let’s walk through the following steps to combine text and numbers and keep formatting in Excel.

Using TEXT Function with Ampersand Operator

📌 Steps:

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

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

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

How to Combine Text and Numbers in Excel and Keep Formatting

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

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

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

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

How to Combine Text and Numbers in Excel and Keep Formatting

  • 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

  • Next, 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

How to Combine Text and Numbers in Excel and Keep Formatting

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

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

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

How to Combine Text and Numbers in Excel and Keep Formatting

🔎 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: Combine Text and Formula in Excel (4 Simple Ways)


2. Combining CONCATENATE and TEXT Functions

Here, we are going to illustrate another effective method to combine text and numbers. We will use CONCATENATE and TEXT functions. For demonstration purposes, we take a dataset containing the Text and Number column. Now we are going to combine text and numbers in Excel. Let’s walk through the following steps to combine text and numbers and keep formatting in Excel.

Combining CONCATENATE and TEXT Functions

📌 Steps:

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

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

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

How to Combine Text and Numbers in Excel and Keep Formatting

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

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

How to Combine Text and Numbers in Excel and Keep Formatting

  • 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

  • Next, 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.
  • Therefore, you will be able to combine text and numbers as shown below

How to Combine Text and Numbers in Excel and Keep Formatting

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

Read More: How to Add Text in Excel Spreadsheet (6 Easy Ways)


Similar Readings


3. Utilizing Microsoft Word

Here, we are going to illustrate another tricky method to combine text and numbers. We will use Microsoft Word. For demonstration purposes, we take a dataset containing the Text and Number column. Now we are going to combine text and numbers in Excel. Let’s walk through the following steps to combine text and numbers and keep formatting in Excel.

📌 Steps:

  • First of all, copy the range of cells B5:C10.

Utilizing Microsoft Word

  • Then paste it into MS Word after opening a new document.
  • Therefore, you will get the following table in MS Word.

How to Combine Text and Numbers in Excel and Keep Formatting

  • Next, select the data from the table and go to the Layout tab.
  • Then, click on Data and select Convert to Text.

  • Consequently, the Convert Table to Text window will appear.
  • Then, select Other in the Separate text with option.
  • Next, click on OK.

  • As a consequence, you will be able to combine the text and numbers in MS Word.
  • Now, copy the data.

How to Combine Text and Numbers in Excel and Keep Formatting

  • Next, paste the data on the Text & Number column in the worksheet as shown below.
  • Finally, you will be able to combine text and numbers in Excel and keep formatting as shown below.

Read More: How to Add Text to Multiple Cells in Excel (10 Easy Methods)


4. Applying Custom Formatting

Here, we are going to illustrate another tricky method to combine text and numbers. We will use Microsoft Word. For demonstration purposes, we take a dataset containing the Text and Number column. Now we are going to combine text and numbers in Excel. Let’s walk through the following steps to combine text and numbers and keep formatting in Excel.

Applying Custom Formatting

📌 Steps:

  • First of all, copy the range of the cells C5:C10.
  • Then, past it on the cells D5:D19 as shown below.

How to Combine Text and Numbers in Excel and Keep Formatting

  • Now, select cell D5 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 “Adam’s Salary increased “ before 0% in the Type box.
  • Then, click on OK.

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

How to Combine Text and Numbers in Excel and Keep Formatting

  • Now, select cell D6 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 is due “ before [$-en-US]d-mmm;@ in Type box.
  • Then, click on OK.

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

  • Now, select cell D7 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 “Total Sales “ before $#,##0 in Type box.
  • Then, click on OK.

How to Combine Text and Numbers in Excel and Keep Formatting

  • As a consequence, 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 consequence, 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.

How to Combine Text and Numbers in Excel and Keep Formatting

  • As a consequence, 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.

How to Combine Text and Numbers in Excel and Keep Formatting

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

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

How to Combine Text and Numbers in Excel and Keep Formatting

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

How to Combine Text and Numbers in Excel and Keep Formatting


💬 Things to Remember

✎ You must not forget to input quotation marks (“ “) around the specified format code.

✎ If you don’t want to use the TEXT function, you can also click on the Number command from the Number group of commands, and then you have to type the format codes by selecting the Custom format option.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to combine text and numbers in Excel and keep the formatting. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website ExcelDemy for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib
1 Comment
  1. Excel: How to CONTATENATE a RANGE Of Cells [Combine]

    Combining values CONCATENATE is the best, but it is not possible to refer all ranges with this function.

    Select all cells in a range individually. If you attempt to refer to the entire range, it will return text from the first cell.

    This situation calls for a method that allows you to refer to a whole range of cells and combine them into one cell.

    Today, I want to share 5 ways to combine text from different ranges into one cell.

    [CONCATENATE + TRAPOSE] to combine Values

    Combining text from multiple cells into one cell with the transpose function and concatenating function is the best way to do this.

    Take a look at the following range of cells. You have a text, but each word is in a different column. You want it all to fit in one cell.

    These are the steps to combine these values into one cell.

    Enter the following formula in the B8 and don’t press enter

    Select the entire concatenate function inside and press F9. It will transform it into an array.

    The curly brackets at the beginning and end of the array should be removed.

    Hit enter at the end. It returns an array when you convert this reference into hard values.

    This array has text in each cell, and space between them. When you hit enter, it will combine them all.

    Combining Text with Fill Justify Option

    Fill justify is an underutilized but powerful tool in Excel. It can be used whenever text is needed to be combined from different cells.

    It only takes one click to merge text. Take a look at the following data and follow these steps.

    This will combine text from all cells into the first cell in the selection.

    TEXTJOIN Function to Concaterate Values

    Excel 2016 (Office 365) has a function called TextJoin. This function allows you to combine text from multiple cells into one cell.

    Syntax: How To Be Able To Combine Numbers

Leave a reply

ExcelDemy
Logo