How to Concatenate Numbers in Excel (4 Quick Formulas)

Get FREE Advanced Excel Exercises with Solutions!

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.


1. Concatenating 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. Concatenating 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. Joining Dates and Times in Excel

In this section, we’ll now concatenate 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. Concatenating 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

Read More: How to Concatenate and Keep Currency Format in Excel


5. Combining 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 as follows 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. Concatenating 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


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Concatenate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo