Method 1 – Using an Excel Formula with Ampersand Operator
1.1 Without Formatting
Steps:
- Select cell D5 and enter the following formula:
=B5&" "&C5- Press Enter.
- You will see an output like the image below.
- Select cell D5 and drag the Fill Handle to the entire column Combined.
- You will see an output like the one below.
1.2 With Formatting
In the dataset, we have different types of texts in the Text column and different types of number formats in the Number column as numbers.
Steps:
- Enter the following formula with the TEXT function in cell D5 to combine text and numbers:
=B5&" "&TEXT(C5,"00%")- Press Enter.
- You will be able to combine text and numbers, as shown below.
- Enter the following formula in cell D6 to combine text and numbers:
=B6 &TEXT(C6,"Dd-mmm")- Press Enter.
- You will be able to combine text and numbers, as shown below.
- Enter the following formula in cell D7 to combine text and numbers:
=B7&" "&TEXT(C7,"$#,##0")- Press Enter.
- You will be able to combine text and numbers, as shown below.
- Enter the following formula in cell D8 to combine text and numbers:
=B8&" "&C8- Press Enter.
- You will be able to combine text and numbers, as shown below.
- Enter the following formula in cell D9 to combine text and numbers:
=B9&" "&TEXT(C9,"d-mmm-yyyy")- Press Enter.
- You will be able to combine text and numbers, as shown below.
- 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")- Press Enter.
- 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.
Method 2 – Combining TEXT and CONCATENATE Functions
Steps:
- Enter the following formula in cell D5 to combine text and numbers:
=CONCATENATE(B5," ",TEXT(C5,"00%"))- Press Enter.
- You will be able to combine text and numbers, as shown below.
- Enter the following formula in cell D6 to combine text and numbers:
=CONCATENATE(B6," ",TEXT(C6,"Dd-mmm"))- Press Enter.
- You will be able to combine text and numbers, as shown below.
- Enter the following formula in cell D7 to combine text and numbers:
=CONCATENATE(B7," ",TEXT(C7,"$#,##0"))- Press Enter.
- You will be able to combine text and numbers, as shown below.
- Enter the following formula in cell D8 to combine text and numbers:
=CONCATENATE(B8," ",C8)- Press Enter.
- You will be able to combine text and numbers, as shown below.
- Enter the following formula in cell D9 to combine text and numbers:
=CONCATENATE(B9," ",TEXT(C9,"d-mmm-yyyy"))- Press Enter.
- You will be able to combine text and numbers, as shown below.
- 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"))- Press Enter.
- 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, the 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, the 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, the 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, the CONCATENATE function combines the texts. Moreover, “ “ is the White Space character in between words.
3. Applying the TEXTJOIN Function
Steps:
- Select cell D5 and enter the following formula:
=TEXTJOIN(" ",TRUE,B5:C5)- Press Enter.
- You will see an output like the image below.
- Select cell D5 and drag the Fill Handle to the entire column Combined.
- You will see an output like the one below. All your texts and numbers will be combined in the column Combined without formatting.
Method 4 – Using Custom Formatting
Steps:
- Copy the range of the cells (C5:C10).
- Paste it on 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 “John’s salary increased “ before 0% in the Type box.
- Click OK.
- You will be able to combine text and numbers, as shown below.
- Select cell D6 and press ‘Ctrl+1’.
- The Format Cells window will appear.
- Select Custom from the Category option.
- Type “Payment is due” before [$-en-US]d-mmm;@ in the Type box.
- Click OK.
- You will be able to combine text and numbers, as shown below.
- Select cell D7 and press ‘Ctrl+1′.
- The Format Cells window will appear.
- Select Custom from the Category option.
- Type “Total sales “ before $#,##0 in the Type box.
- Click OK.
- You will be able to combine text and numbers, as shown below.
- Select cell D8 and press ‘Ctrl+1’.
- The Format Cells window will appear.
- Select Custom from the Category option.
- Type “Kane’s score is “ before General in the Type box.
- Click OK.
- You will be able to combine text and numbers, as shown below.
- Select cell D9 and press ‘Ctrl+1′.
- The Format Cells window will appear.
- Select Custom from the Category option.
- Type “Payment completed” before [$-en-US]d-mmm-yyyy;@ in the Type box.
- Click OK.
- You will be able to combine text and numbers, as shown below.
- Select cell D10 and press ‘Ctrl+1’.
- The Format Cells window will appear.
- Select Custom from the Category option.
- Type “Today is “ before m/d/yyyy in the Type box.
- Click OK.
- You will be able to combine text and numbers, as shown below.
Things to Remember
- If you want to combine text and numbers without formatting, then using the Excel formula with the Ampersand operator and TEXTJOIN function will be a better option.
- If you have different types of number formats and want to keep those formats, you can use the TEXT function, a combination of the TEXT and CONCATENATE functions, or custom formatting.
Download the Practice Workbook
You can download the Excel workbook from here.
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
- How to Combine Date and Text in Excel
- How to Concatenate and Keep Currency Format in Excel
<< Go Back to Concateante Numbers in Excel | Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
