How to Separate Numbers in a Cell in Excel

How you separate numbers in an Excel cell depends on what the cell contains. If the numbers are divided by a character, as in 120,450,980 or 555-123-4567, use Data > Text to Columns, or =TEXTSPLIT(A2,",") in Microsoft 365. To put each digit of a number in its own cell, use =MID(A2,SEQUENCE(1,LEN(A2)),1). To pull numbers out of text, as in Order 4521, use Flash Fill (Ctrl + E) or =REGEXEXTRACT(A2,"[0-9]+"). Here’s each method with examples.

Which method do you need?

Cell contains	You want	Best method
120,450,980	120 | 450 | 980	Text to Columns or TEXTSPLIT
5551234567	555 | 123 | 4567	Text to Columns (Fixed width) or LEFT/MID/RIGHT
2026	2 | 0 | 2 | 6	MID with SEQUENCE
Order 4521 shipped	4521	Flash Fill or REGEXEXTRACT

Method 1: Text to Columns (any version)

Use this when a character such as a comma, space, dash or slash separates the numbers.

1. Select the cells. Make sure the columns to the right are empty, because the results will overwrite them.
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next.
4. Tick the separator, such as Comma or Space. For a dash or slash, tick Other and type it. Click Next.
5. To keep leading zeros (such as 007), select those columns in the preview and choose Text under Column data format.
6. Pick a Destination cell if you want to keep the original data, and click Finish.

Split at fixed positions

For numbers with no separator, such as a 10-digit phone number, choose Fixed width in step 3. Then click in the preview to place a break line after the third and sixth digits.

Text to Columns is a one-time action: if the original cell changes, the split cells don’t update. The same tool is used to split columns in Excel.

Method 2: TEXTSPLIT (Microsoft 365)

TEXTSPLIT is a formula, so its results update automatically.

• Into columns: =TEXTSPLIT(A2,",")
• Into rows: =TEXTSPLIT(A2,,",")
• Several separators at once: =TEXTSPLIT(A2,{",","-"," "})

The results are text. To turn them into real numbers you can add up, wrap the formula: =--TEXTSPLIT(A2,",") or =VALUE(TEXTSPLIT(A2,",")).

Older versions: LEFT, MID and RIGHT

For fixed positions, formulas work in any version. For a phone number in A2: =LEFT(A2,3), =MID(A2,4,3) and =RIGHT(A2,4). If A2 holds a number rather than text, these return text. Add -- in front if you need numbers.

Method 3: Put each digit in its own cell

In Microsoft 365 or Excel 2021 and later, one formula spills every digit across a row:

=--MID(A2,SEQUENCE(1,LEN(A2)),1)

SEQUENCE creates the positions 1, 2, 3 and so on, up to the length of the number. MID takes one character at each position, and -- turns each one back into a number.

In older versions, type =--MID($A2,COLUMN(A1),1) in the first cell and copy it to the right. COLUMN(A1) returns 1, then 2, 3 and so on as you copy across. Stop when you get #VALUE!, which means you’ve passed the last digit.

Add up the digits

To total the digits without splitting them, use =SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)). For 2026, it returns 10.

Method 4: Pull numbers out of text

Flash Fill (Excel 2013 and later)

1. In the column next to your data, type the number from the first cell exactly as you want it, for example 4521.
2. Click the cell below it and press Ctrl + E.

Excel spots the pattern and fills the rest. Check a few results, because Flash Fill can guess wrong when the text varies a lot. Like Text to Columns, it doesn’t update automatically.

REGEXEXTRACT (Microsoft 365)

• First number in the cell: =REGEXEXTRACT(A2,"[0-9]+")
• Every number, spilled across cells: =REGEXEXTRACT(A2,"[0-9]+",1)
• Numbers with decimals: =REGEXEXTRACT(A2,"[0-9]+[.]?[0-9]*")

Other patterns are covered in using REGEXEXTRACT in Excel.

Older versions: keep only the digits

In Excel 2019 and later, this formula removes everything except the digits:

=TEXTJOIN("",TRUE,IFERROR(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

It joins all the digits together, so A12 B34 becomes 1234. If you only want to strip certain characters, remove characters from a cell in Excel instead.

Split text and numbers into two columns

For codes like ABC123 or Room45, where the letters come first and the number follows, you can split the cell into a text part and a number part.

Microsoft 365

• Text part: =REGEXEXTRACT(A2,"^[^0-9]+")
• Number part: =--REGEXEXTRACT(A2,"[0-9]+$")

Any version

1. Find where the first digit is: in B2, type =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")). Adding “0123456789” to the end of the text makes sure FIND always succeeds, so cells with no digits don’t cause errors.
2. Text part in C2: =LEFT(A2,B2-1)
3. Number part in D2: =--MID(A2,B2,LEN(A2))

Copy the formulas down. For ABC123, B2 returns 4, C2 returns ABC and D2 returns 123. If some cells have no number, D2 shows #VALUE!. Wrap it in IFERROR to show a blank instead: =IFERROR(--MID(A2,B2,LEN(A2)),"").

This works for one block of letters followed by one block of digits. For messier text, use REGEXEXTRACT or Power Query.

Power Query for large or repeating jobs

If you split the same kind of data every week, Power Query saves the steps. Select the data and go to Data > From Table/Range. Then right-click the column and choose Split Column. Options include By Delimiter, By Number of Characters and By Non-Digit to Digit, which separates text from numbers. Click Close & Load, and use Refresh next time.

Troubleshooting

Leading zeros disappear

Excel treats 007 as the number 7. Set the destination columns to Text in Text to Columns, or keep the formula results as text by leaving out --.

Long numbers turn into 1.23E+15

Excel only keeps 15 significant digits. Format the source cells as Text before typing or pasting long IDs or card-style numbers.

#NAME? error

Your version doesn’t have TEXTSPLIT, SEQUENCE or REGEXEXTRACT. Use Text to Columns, Flash Fill or the older formulas above.

Related guides

• How to separate names into two columns in Excel
• How to separate city, state and ZIP in Excel

Summary

1. Numbers with a separator: Text to Columns or =TEXTSPLIT(A2,",").
2. Each digit: =--MID(A2,SEQUENCE(1,LEN(A2)),1).
3. Numbers inside text: Ctrl + E or =REGEXEXTRACT(A2,"[0-9]+").
4. Add -- to turn text results into numbers.