Function Problems

How to Split a Number into Two Cells in Excel (Using Formulas)

Huma Ashraf Ch Oct 12, 2026 870 views

Question details

The user needs a formula to split a single multidigit number across two different cells, allocating the five rightmost digits to one cell and the remaining leftmost digits to another.

Product: Excel
Device & OS: not provided
Scenario: When formatting or extracting specific segments of a long numeric string (like an ID number, serial code, or financial record) for separate reporting or analysis.
Observed behavior: The user wants to distribute a single multidigit number into two target cells dynamically based on a fixed character count from the right.

Before you start

Ensure that the source cell contains the numbers as a continuous numeric string without spaces. If the total digit count of any cell might be fewer than five, you may need to add error handling to prevent formula errors.

Solution 1Recommended

Use Text Functions (LEFT, RIGHT, LEN) to Split the Number

Extract the rightmost five digits and the remaining leftmost digits using standard Excel text manipulation formulas.

The RIGHT function easily extracts a specific number of characters from the end of a string. To get the remaining characters from the beginning, use the LEFT function combined with the LEN function to calculate the total string length minus the rightmost digits.

Extract the remaining leftmost digits

Select the cell where you want the leftmost remaining digits to appear (e.g., B2). Type the formula =LEFT(A2,LEN(A2)-5) and press Enter.

Extract the rightmost five digits

Select the next cell where you want the rightmost five digits to appear (e.g., C2). Type the formula =RIGHT(A2,5) and press Enter.

Apply formulas to the entire column

Select both B2 and C2, then click and drag the fill handle (the small square at the bottom-right corner of the selection) down to apply these formulas to the rest of your data.

Use Text Functions (LEFT, RIGHT, LEN) to Split the Number

Convert Text Results to Numbers: Text functions return values as text strings. If you need the results to act as actual numbers for future calculations, add a double negative before the function: =--LEFT(A2,LEN(A2)-5) and =--RIGHT(A2,5).

Solution 2

Use Flash Fill for Quick Extraction

Use Excel's Flash Fill feature to automatically split the numbers without typing any formulas.

Efficient Data Processing

Split Numbers Easily in WPS Spreadsheet

WPS Spreadsheet provides seamless support for all standard text functions like LEFT, RIGHT, and LEN, making data extraction fast and accurate. It perfectly handles large datasets and complex text-to-number conversions.

1. Open your data file: Launch WPS Spreadsheet and open the document containing the multidigit numbers.
2. Extract the rightmost digits: Select an empty cell and enter the formula =RIGHT(A2,5) to extract the last 5 digits.
3. Extract the remaining digits: In the adjacent cell, enter =--LEFT(A2,LEN(A2)-5) to extract the remaining digits and convert them to numeric values.
4. Fill down the data: Drag the green fill handle downwards to apply the split logic to all remaining rows.

Fully compatible with Microsoft Excel files (.xlsx) and all standard formulas.Includes a smart Flash Fill feature for instantaneous data separation.Lightweight, fast, and completely free to use for everyday data processing.Familiar user interface requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why do the split numbers align to the left side of the cell?

By default, text functions like LEFT and RIGHT return text strings, which align to the left in a cell. To convert them back to numeric values (which automatically align to the right), place two minus signs (a double unary) in front of the formula, such as =--RIGHT(A2,5).

What happens if the original number has fewer than five digits?

If the number has fewer than five digits, the =LEFT(A2,LEN(A2)-5) formula will return a #VALUE! error because subtracting 5 from the total length results in a negative number. You can wrap the formula in an IFERROR function to handle this gracefully.

Can I split the number using the Text to Columns feature instead of formulas?

Yes. Select your data column, go to the Data tab, and click 'Text to Columns'. Choose 'Fixed width', click next, and place the column break line exactly five characters from the right. Finish the wizard to split the data directly across two columns without using any formulas.

Excel & Data Problems