Concatenate Numbers in Excel (4 Quick Formulas)

In Microsoft Excel, there are numerous ways available to concatenate or join numbers to display in a single cell. The numbers can be of different formats and that’s why we have to insert an additional function to specify the formats of the numbers while combining them. In this article, you’ll find all possible methods and formulas to concatenate numbers with simple examples and proper illustrations.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


6 Suitable Methods to Concatenate Numbers in Excel

1. Concatenate Numeric Values in Excel

In our first example, we’ll show how to concatenate simple numerical values in Excel. In the following picture, two values are lying in B5 and C5.

In the output box, we’ll combine these numerical values by adding a conjunction ‘and’ between them. We’ll use Ampersand (&), TEXTJOIN, CONCATENATE, and CONCAT functions separately to generate the outputs. The related formulas will be shown under the Formulas header for the corresponding outputs.

Concatenate Numeric Values in Excel

The output cells ranging from B8 to B11 occupy the following successive formulas:

With the use of Ampersand (&) only:

=B5&" and "&C5

With the CONCATENATE function:

=CONCATENATE(B5," and ",C5)

With the CONCAT function:

=CONCAT(B5," and ",C5)

And with the TEXTJOIN function:

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

Concatenate Numeric Values in Excel


2. Connect Fraction Numbers in Excel

Now we’ll concatenate two fraction numbers with the conjunction ‘and’. We’ll have to use the TEXT function here to specify and maintain the format of the fractions.

Connect Fraction Numbers in Excel

With the uses of the TEXT function and Ampersand (&), the formula used in Cell B8 is:

=TEXT(B5,"#/#")&" and "&TEXT(C5,"# 0/0")

With the use of CONCATENATE function:

=CONCATENATE(TEXT(B5,"#/#")," and ",TEXT(C5,"# 0/0"))

With the use of CONCAT function:

=CONCAT(TEXT(B5,"#/#")," and ",TEXT(C5,"# 0/0"))

And with the use of TEXTJOIN function:

=TEXTJOIN(" and ",TRUE,TEXT(B5,"#/#"),TEXT(C5,"# 0/0"))

Connect Fraction Numbers in Excel


3. Join Dates and Times in Excel

In this section, we’ll now combine a date and a time. Again we have to use the TEXT function here to specify the formats of the date and time. Otherwise, the dates and times will show their corresponding serial numbers only that are assigned in Excel.

Join Dates and Times in Excel

We’ll again use four types of formulas here to concatenate date and time. The date and the time will be separated by a comma (,). The formulas that can be used to combine the date and the time are as follows.

With the combination of Ampersand (&) and the TEXT function only, the formula will be:

=TEXT(B5,"DD-MM-YYYY")&", "&TEXT(C5,"HH:MM:SS AM/PM")

With the uses of CONCATENATE and CONCAT functions:

=CONCATENATE(TEXT(B5,"DD-MM-YYYY"),", ",TEXT(C5,"HH:MM:SS AM/PM"))

Or,

=CONCAT(TEXT(B5,"DD-MM-YYYY"),", ",TEXT(C5,"HH:MM:SS AM/PM"))

And with the application of the TEXTJOIN function:

=TEXTJOIN(", ",TRUE,TEXT(B5,"DD-MM-YYYY"),TEXT(C5,"HH:MM:SS AM/PM"))

Join Dates and Times in Excel


4. Concatenate Currency Values in Excel

Now we’ll concatenate two currencies with the conjunction ‘and’. As the currency values are in dollars, we’ll have to use the TEXT function to specify the format of the amounts as well as the symbol of the dollar ($).

Concatenate Currency Values in Excel

With the use of Ampersand (&) and TEXT function, the required formula in the first output Cell B8 will be:

=TEXT(B5,"$ 0,000.00")&" and "&TEXT(C5,"$ 0,000.00")

With the uses of CONCATENATE or CONCAT function:

=CONCATENATE(TEXT(B5,"$ 0,000.00")," and ",TEXT(C5,"$ 0,000.00"))

Or,

=CONCAT(TEXT(B5,"$ 0,000.00")," and ",TEXT(C5,"$ 0,000.00"))

And with the application of the TEXTJOIN function, the formula is as follows:

=TEXTJOIN(" and ",TRUE,TEXT(B5,"$ 0,000.00"),TEXT(C5,"$ 0,000.00"))

The resultant data are visible under the Output header in Column B.

Concatenate Currency Values in Excel


5. Combine Percentage Numbers in Excel

To concatenate two percentage values, we have to again specify the format of the numeric values and the percentage symbol (%) with the TEXT function. The procedure is the same as described in the previous method where we had to define the dollar symbol and the format of the numeric amounts.

Combine Percentage Numbers in Excel

With the uses of Ampersand (&) and other related functions, the required formulas are as follows that have led to similar results under the Output header in Column B:

=TEXT(B5,"00.00 %")&" and "&TEXT(C5,"00.00 %")

With the uses of CONCATENATE or CONCAT function:

=CONCATENATE(TEXT(B5,"00.00 %")," and ",TEXT(C5,"00.00 %"))

Or,

=CONCAT(TEXT(B5,"00.00 %")&" and "&TEXT(C5,"00.00 %"))

And by applying the TEXTJOIN function:

=TEXTJOIN(" and ",TRUE,TEXT(B5,"00.00 %"),TEXT(C5,"00.00 %"))

Combine Percentage Numbers in Excel


6. Concatenate Numbers Formatted as Scientific Notations

In our last example, we’ll combine two scientific numbers that include exponents. The numbers are lying in B5 and C5.

Concatenate Numbers Formatted as Scientific Notations

With the use of the TEXT function, we have to specify the formats of the scientific notations. By using Ampersand (&) and the TEXT function, the required formula should be as follows:

=TEXT(B5,"0.00E+00")&" and "&TEXT(C5,"0.00E+00")

Applying the CONCATENATE or CONCAT function will construct the following formulas:

=CONCATENATE(TEXT(B5,"0.00E+00")," and ",TEXT(C5,"0.00E+00"))

Or,

=CONCAT(TEXT(B5,"0.00E+00")," and ",TEXT(C5,"0.00E+00"))

And with the use of the TEXTJOIN function, the related formula will be:

=TEXTJOIN(" and ",TRUE,TEXT(B5,"0.00E+00"),TEXT(C5,"0.00E+00"))

Concatenate Numbers Formatted as Scientific Notations


Concluding Words

I hope all of the formulas and procedures mentioned above will now help you to apply them in your Excel spreadsheets when you have to concatenate numbers. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

Lookup and Return Multiple Values Concatenated into One Cell in Excel

How to Concatenate Multiple Cells in Excel

How to Concatenate in Excel (3 Suitable Ways)

How to Use CONCATENATE Function in Excel (4 Examples)

How to Concatenate Two Columns In Excel ( 5 Simple Methods)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo