Function Problems

How to Combine Two Numbers into a 16-Digit Value in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to merge two separate numerical values (such as 1644182024 and 240001) into one continuous 16-digit text string or identifier (1644182024240001).

Product: Excel
Device & OS: not provided
Scenario: Combining data from two separate columns into a single column to create a unique 16-digit identifier.
Observed behavior: The goal is to successfully concatenate the two numbers without Excel altering the long 16-digit string into scientific notation or losing the trailing digits due to precision limits.

Before you start

Ensure that the cells where your final 16-digit value will appear are formatted as 'Text' beforehand. This prevents Excel from rounding the 16th digit to zero, as standard numeric precision is limited to 15 digits.

Solution 1Recommended

Use the Ampersand (&) Operator to Combine Numbers

The simplest and most direct method to join two cell values together is by using the ampersand (&) operator within a formula.

This method creates a text string out of your numeric values. Since Excel has a 15-digit limit for numerical calculations, joining numbers to create a 16-digit value must be handled as text to avoid precision loss or scientific notation.

Select the Target Cell

Click on an empty cell where you want the combined 16-digit value to appear (for example, cell C2).

Enter the Concatenation Formula

Type the formula =A2&B2 into the formula bar, assuming your first number is in cell A2 and your second number is in cell B2.

Press Enter and Format as Text

Press Enter to apply the formula. If the resulting value displays in scientific notation (e.g., 1.64E+15), right-click the cell, select 'Format Cells', choose 'Text', and then re-enter the formula.

Copy the Formula Down

Click the small green square at the bottom-right corner of cell C2 and drag it down to apply the combination formula to the rest of the rows in your dataset.

Preserving Leading Zeros: If your second number contains leading zeros that must be kept (e.g., 002401), you can use the TEXT function to preserve them, like this: =A2&TEXT(B2, "000000").

Solution 2

Use the CONCAT or CONCATENATE Function

You can achieve the same result using built-in Excel functions designed specifically to join multiple strings into one.

Seamless Data Manipulation

Easily Combine and Manage Large Numbers in WPS Spreadsheet

WPS Spreadsheet provides robust support for all standard data manipulation formulas, including the ampersand operator and CONCAT function. It effortlessly handles long text strings and identifiers, allowing you to manage large datasets with total precision.

1. Open Your Data in WPS: Launch WPS Office and open your spreadsheet containing the numbers you wish to combine.
2. Pre-format the Result Column: Highlight the column where your combined values will go, right-click, select 'Format Cells', and set it to 'Text'.
3. Input the Formula: Click the first cell of your target column and enter =A2&B2.
4. Drag to Fill: Use the fill handle at the bottom right of the cell to drag the formula down to the remaining rows.

Fully compatible with Microsoft Excel formulas like CONCATENATE and the & operator.Easily format cells as Text to preserve exact 16-digit identifiers without data loss.Lightweight software that handles heavy datasets smoothly and efficiently.Free to download and use for your daily office and spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the last digit of my 16-digit number change to zero?

Excel and similar spreadsheet programs have a 15-digit precision limit for numerical values. Any digit beyond the 15th is automatically rounded to zero if the program treats the data as a number. To fix this, you must format the destination cell as 'Text' before combining the values, or ensure the formula outputs a text string.

Why is my combined number showing as scientific notation (e.g., 1.64E+15)?

Spreadsheets automatically display numbers with 12 or more digits in scientific notation to save space. Since your combined number is 16 digits long, this default display applies. Change the cell format to 'Text' to display the full 16-digit sequence correctly.

Can I add a hyphen or space between the two numbers when combining them?

Yes. To insert a character like a hyphen or a space between the values in A2 and B2, enclose the character in quotation marks within your formula. For example, use =A2&"-"&B2 to separate them with a hyphen, or =A2&" "&B2 for a space.

Excel & Data Problems