Method 1 – Using the TEXT Function with the Ampersand Operator
We have a dataset containing the Text and Number column. We are going to combine text with those numbers in Excel.
Steps:
- Enter the following formula in cell D5 to combine text and numbers.
=B5&" "&TEXT(C5,"0.00%")
- Press Enter.
- Enter the following formula in cell D6 to combine text and dates.
=B6 &TEXT(C6,"Dd-mmm")
- Press Enter.
- Enter the following formula in cell D7 to combine text and numbers.
=B7&" "&TEXT(C7,"$#,##0")
- Press Enter.
- Enter the following formula in cell D8 to combine text and numbers.
=B8&" "&C8
- Press Enter.
- Enter the following formula in cell D9 to combine text and date.
=B9&" "&TEXT(C9,"d-mmm-yyyy")
- Press Enter.
- Enter the following formula in cell D10 to combine text and today’s date.
=B10 & " "&TEXT(TODAY(),"mm-dd-yyy")
- Press Enter.
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 Combine Text and Number in Excel
Method 2 – Combining CONCATENATE and TEXT Functions
We’ll use the same dataset with some strings in column B and various numbers or dates in column C.
Steps:
- Enter the following formula in cell D5 to combine text and numbers.
=CONCATENATE(B5," ",TEXT(C5,"00%"))
- Enter the following formula in cell D6 to combine text and date.
=CONCATENATE(B6," ",TEXT(C6,"Dd-mmm"))
- Enter the following formula in cell D7 to combine text and numbers.
=CONCATENATE(B7," ",TEXT(C7,"$#,##0"))
- Enter the following formula in cell D8 to combine text and numbers.
=CONCATENATE(B8," ",C8)
- Enter the following formula in cell D9 to combine text and date.
=CONCATENATE(B9," ",TEXT(C9,"d-mmm-yyyy"))
- Enter the following formula in cell D10 to combine text and today’s date.
=CONCATENATE(B10," ",TEXT(TODAY(),"mm-dd-yyy"))
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”) unction 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, the 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 Combine Date and Text in Excel
Method 3 – Using Microsoft Word
Steps:
- Copy the range of cells B5:C10.
- Paste it into MS Word.
- You will get the following table in MS Word.
- Select the data from the table and go to the Layout tab.
- Click on Data and select Convert to Text.
- The Convert Table to Text window will appear.
- Select Other in the Separate text with option.
- Click on OK.
- Copy the data.
- Paste the data in the Text & Number column in the worksheet as shown below.
Method 4 – Applying Custom Formatting
We’ll use the same dataset.
Steps:
- Copy the range of the cells C5:C10.
- Paste it in the cells D5:D19 as shown below.
- Select cell D5 and press Ctrl + 1.
- The Format Cells window will appear.
- Select Custom from the Category option.
- Type “Adam’s Salary increased “ before 0% in the Type box.
- Click on OK.
- This manually inserts the text as formatting.
- Repeat for the next cell, typing “Payment is due “ before [$-en-US]d-mmm;@ in Type box.
- Here’s the result.
- For D7, use “Total Sales “ before $#,##0 in Type box.
- Here’s the result.
- For D8, use “Kane’s Score is “ before General in the Type box.
- Here’s the result.
- For cell D9, use “Payment Completed “ before [$-en-US]d-mmm-yyyy;@ in Type box.
- Here’s the result.
- For cell D10, use “Today is “ before m/d/yyyy in the Type box.
- Here’s the result.
Read More: How to Concatenate and Keep Currency Format in Excel
Download the Practice Workbook
Related Articles
- How to Concatenate Numbers in Excel
- How to Add a 1 in Front of Numbers in Excel
- How to Concatenate Numbers with Leading Zeros in Excel
- How to Add Leading Zeros in Excel by CONCATENATE Operation
- How to Concatenate and Keep Number Format in Excel
- How to Concatenate Date That Doesn’t Become Number in Excel
<< Go Back to Concateante Numbers in Excel | Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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
Thank you so much for sharing this information with me.