How to split numbers into columns in Excel?
When working with large datasets in Excel, you may encounter situations where you have a list of numbers—each potentially lengthy—and need to separate each digit of these numbers into individual columns. For instance, a cell containing "9487" would be split so that each digit occupies its own column. This requirement often arises in data analysis, digit-level processing, coding tasks, and situations where each digit carries a specific meaning, such as codes, labels, or statistics.
This tutorial presents several practical approaches to quickly and efficiently split numbers into separate columns in Excel.
Split numbers to separate columns with Text to Columns
Split numbers to separate columns with Kutools for Excel 
Split numbers to separate columns using Excel Formula (MID and COLUMN/ROW)
Split numbers to separate columns using VBA Macro
Split numbers to separate columns with Text to Columns
In Excel, you can utilize the Text to Columns feature to efficiently separate each digit of a number into its own column, especially for numbers of consistent or predictable length.
Applicable scenario: Best for numbers with uniform digit counts, or smaller datasets where manual intervention to set split positions is feasible. Less ideal for numbers of varying lengths or very large datasets.
1. Select the cells containing the numbers you want to split. Then, navigate to the Data tab and click Text to Columns.
2. The Convert Text to Columns Wizard will appear. To split numbers by each digit, select Fixed width and click Next.
3. In the next step, click to add a break line between each digit. Be sure to place divider lines exactly where you want each digit separated. For example, if your numbers all have four digits, add a split after the first, second, and third digit.
Tip: If your numbers differ in length, this method may cause misalignment.
4. Click Next to move to the final step. Choose a destination cell for your output—this determines where the split data will begin appearing in columns. Select a starting cell that has enough empty columns to its right, so your data is not overwritten.
5. Click Finish; each digit will now appear in a separate column. Check your results for any digits merged or dropped if the breaks weren't set correctly.
Tips and troubleshooting:
- If you receive an error or unexpected result, double-check your break lines and make sure the target area is empty to avoid overwriting data.
- This method is manual; for data with variable-length numbers, consider other approaches outlined below.
Split numbers to separate columns with Kutools for Excel
Kutools for Excel provides the Split Cells utility, allowing you to split number digits into separate columns quickly, even for large or complex datasets. This option is especially useful when you want convenience and additional features like splitting by specific width or processing existing data without manual break lines.
After free installing Kutools for Excel, please proceed as follows:
1. Select the cells containing the numbers to split, then on the Kutools tab, click Merge & Split > Split Cells. This will open the Split Cells dialog.
2. In the Split Cells dialog, check Split to Columns under the Type section. Then, under Split by, select Specify width and enter 1 in the textbox. This ensures that each digit is separated into its own column.
3. Click OK. A dialog box will prompt you to select a destination cell for the output. Choose a cell with enough empty columns to the right, then click OK. The number digits will be split into individual columns as displayed below.
With the Split Cells utility of Kutools for Excel, you can also split cell contents by other patterns, including splitting a cell into multiple rows if needed.
Advantages: Fast batch processing, less prone to manual splitting errors, supports various splitting options, and integrates smoothly with existing workflow.
Notes: If your numbers contain decimal points or other non-digit characters, check your output—those will be split as well. Always double-check your range selection and output cell to avoid overwriting important data.
Split numbers to separate columns using Excel Formula (MID and COLUMN/ROW)
For scenarios where you want a dynamic, formula-based approach that updates as the source data changes, you can use Excel formulas to extract each digit of a number into separate columns. This is particularly valuable for repeating tasks, automation, or when numbers vary in length between rows.
Applicable scenario: Best when numbers are all in one column, need to be split across several columns, and you want formulas to maintain live linkage to the original data. Well-suited for datasets of any size. However, careful handling of numbers with leading zeros or non-numeric characters is required.
1. In the first output cell to the right of your number (for example, if your number is in A2, enter this in B2), type the following formula to get the first digit:
=MID($A2, COLUMN(A1),1)2. Press Enter to confirm the formula. Then, drag the fill handle to the right for as many columns as there are digits in the longest number from your dataset. Each column will extract the corresponding digit.
Explanation:
- The
MIDfunction extracts a character from a string at the indicated position. COLUMN(A1)returns 1 in the first output column (i.e., B), 2 in the next (C), and so on, matching the digit position to extract.- Using
$A2anchors the formula to your number cell in row 2. Copy downwards to process more rows.
Tips:
- If your numbers are formatted as numbers (not text), leading zeros will not appear—consider converting to text first if those are significant.
- The formula will return empty results in columns going beyond the actual digit count of shorter numbers.
Troubleshooting: If you see unexpected characters or errors, double-check the reference cell and that the formula is adjusted for the correct row and column. Use VALUE() to force numeric output if needed.
Split numbers to separate columns using VBA Macro
If you have a large dataset or want extra automation and flexibility (such as handling leading zeros, variable-length numbers, or complex parsing needs), a VBA macro is an efficient option. The macro below splits each digit of a number into adjacent columns for each row in your selected range. This is especially useful when repeating the task across many rows or needing to tailor the behavior beyond standard Excel features.
Applicable scenario: Ideal for processing extensive lists and automating repetitive work. Provides more control for customizing behavior (e.g., handling of non-standard characters or advanced pre/post-processing). Requires enabling macros and some basic familiarity with Excel's VBA environment.
1. To set up the macro, go to Developer Tools > Visual Basic, then in the Microsoft Visual Basic for Applications window, click Insert > Module. Copy and paste the code below into the module window:
Sub SplitNumberToColumns()
Dim WorkRng As Range
Dim Rng As Range
Dim i As Integer
Dim CellValue As String
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to split digits:", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
CellValue = CStr(Rng.Value)
For i = 1 To Len(CellValue)
Rng.Offset(0, i).Value = Mid(CellValue, i, 1)
Next i
Next Rng
End Sub2. Click the 
button to execute the macro. A dialog will appear for you to select the range of numbers to process. For each selected cell, its digits will be split into adjacent columns, one digit per column to the right of the original value.
Troubleshooting and guidance:
- If a cell is empty or contains non-numeric data, the macro still processes it according to string length. Manually review the output if your data is mixed.
- To process all numbers without overwriting, ensure empty columns are available to the right of your selection.
- If you receive a macro security alert, enable all macros under Excel's trust settings before running the code.
- Undo (Ctrl+Z) is unavailable for macro actions; back up your data beforehand as a best practice.
Demo: Break or split number into individual digits in Excel
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in