How to split a cell at the first number in Excel?
In day-to-day tasks using Excel, splitting a cell’s contents is often necessary to organize or analyze data more effectively. Traditionally, Excel users segment text using delimiters (such as commas or spaces) or by specifying a fixed width. However, there are scenarios in which your text strings contain both text and numbers, and you need to separate the string where the first number appears—a case illustrated in the screenshot below.
When dealing with such data, for example product codes, combined labels, or reference strings, extracting the text before the first number—or the number itself—can help with sorting, searching, and further processing. Below you will find practical methods to split a cell at the first number, including formula-based techniques and a combination of Word’s Find and Replace feature with Excel’s Text to Columns tool. Each approach has its ideal usage scenarios and considerations, which are detailed below to help you select the best solution for your needs.
➤ Split a cell at the first number with formulas
➤ Split a cell at the first number with Word and Text to Columns
➤ Split a cell at the first number using VBA code
Split a cell at the first number with formulas
If you want to split cell contents at the point where the first number appears, you can achieve this directly in Excel using formulas, without requiring any additional tools. This method works best when your data follows a consistent pattern and you want an automated solution that updates as the source data changes.
At a glance
- Advantages: Stays in Excel; dynamic (updates when the original cell changes); suitable for repeated or batch operations via fill handle.
- Limitations: Formulas can be complex; unexpected symbols or non-standard numerals (e.g., full-width digits) may require adjustments.
1) Select a cell for the text portion of your split result (e.g., B1). Enter the following formula to extract the text before the first number from A1:
=TRIM(LEFT(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789")) - 1))Press Enter to confirm, then drag the fill handle down to apply the formula to other rows, extracting the text part for each string as shown below.
2) To extract the remainder starting from the first number, select an adjacent cell (e.g., C1) and enter:
=TRIM(REPLACE(A1, 1, LEN(B1), ""))Again, press Enter and drag the fill handle down to fill the formula for the remaining rows. The result will be the number (and any following characters) extracted from each string.
Parameters explained:
LEFT(A1, …): isolates the left-side text up to (but not including) the first digit.MIN(FIND({0-9}, A1 & "0123456789")): scans for any digit and returns the earliest position; appending"0123456789"ensures a match even if A1 contains no digits.TRIM(…): removes extra spaces around the extracted parts.REPLACE(A1, 1, LEN(B1), ""): removes the left text (length equals the result in B1) to return the remainder starting at the first digit.VALUE(…): convert the extracted number string to a numeric value if needed for calculations.
Troubleshooting and tips
- No digits present: The first formula returns the whole text; the second formula returns blank. Wrap with
IF/IFERRORfor custom outputs. - Dependencies: The second formula relies on the text extracted in B1. Update references if your layout differs.
- Non-standard numerals: If your data contains full-width or non-ASCII digits, normalize them first or extend the digit set in
FIND. - Performance: For very large ranges, consider filling formulas down once, then copy > Paste Special ▸ Values to freeze results.
- Data hygiene: Remove leading/trailing spaces in the source with
TRIMorCLEANif unexpected results occur.
Quickly Split one cell into columns or rows based on delimiter |
| In Excel, to split a cell into columns is tedious with the Wizard step by step. But with Kutools for Excel's Split Cells utility, you can: 1,convert one cell into columns or rows based on delimiter; 2,convert string into text and number; 3,convert string based on specific width, with clicks. Click for full-featured 30 days free trial! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Split a cell at the first number with Word and Text to Columns
In some cases, your data may contain longer or more varied strings, or you might want a solution not based on formulas—especially if you intend to perform one-off or batch processing for a report. A helpful workaround involves combining Microsoft Word’s Find and Replace utility with Excel’s Text to Columns. This approach makes use of Word’s wildcard (regex-like) capabilities to quickly introduce a delimiter at the split point.
At a glance
- Advantages: Handles large datasets; works when formulas are cumbersome; offers flexible manipulation.
- Limitations: Requires both Word & Excel; results are static (not dynamic); and steps are manual. Also, the basic wildcard example below adds a delimiter after every number group, not only the first.
If your strings consist of numbers followed by text (or vice versa), here’s how you can proceed:
1. Copy your target strings in Excel and paste them into a blank Word document. Next, press Ctrl + H to open the Find and Replace dialog.
2. In the Find and Replace dialog, input ([0-9]{1,}) into the Find what box, then type \1, into the Replace with box. Click More to expand options, and tick Use wildcards.
Parameter notes:
- ([0-9]{1,}) finds any group of one or more digits (Word wildcard syntax).
- \1, places a comma after each matched number group, acting as a new delimiter.
3. Click Replace All. Respond with Yes and OK if confirmation dialogs appear.
 |  |  |
This will add a comma after each number, preparing the data for easy splitting in Excel.
4. Copy the modified text from Word back into your Excel worksheet. Then go to Data > Text to Columns.
5. In the Text to Columns wizard, select Delimited, click Next, and choose Comma as your delimiter.
 | |  |
6. Click Next, choose where you want to place the split results, and then click Finish.
Your data will now appear in separate columns—split at the first number (assuming each row contains only one numeric group).
Troubleshooting and tips
- Only the first number? The simple wildcard
([0-9]{1,}) → \1,adds a comma after every number group. Use this method only when each string has a single numeric group. Otherwise consider the formula method or Power Query to split at the first number only. - Add comma before the number: If the first part is text and you want the delimiter before the digits, use
,\1in Replace with. (Example image fixed: “first part is text”.) - Characters & locales: If digits include non-ASCII/full-width forms, Word’s wildcards may miss them. Normalize characters first or handle them in Excel/Power Query.
- Data safety: Work on a copy before replacing. After reimporting to Excel, use Text to Columns carefully to avoid overwriting adjacent data.
- Static results: This method is not dynamic; if your source changes often, prefer the formula approach so results update automatically.
Summary suggestion: If you need the process to update automatically as data changes, use the formula method above. This Word/Excel method is more suitable for one-off data cleaning or when formulas become too complex for your scenario.
Split a cell at the first number using VBA code
As another powerful option for splitting strings at the first number, you can use an Excel VBA macro. This method is recommended for users comfortable with macros, especially when processing variable-length data or automating repetitive splitting tasks across large datasets. Using VBA allows for more customization and flexibility than formulas, although it requires enabling macros in Excel and some basic code editing steps.
At a glance
- Benefits: Highly customizable, scalable for batch processing, minimizes manual steps.
- Limitations: Requires macro permissions and some VBA know-how; results are not as instantly dynamic as formulas.
1. Open Excel and press Alt + F11 to open the VBA editor. In the VBA interface, click Insert > Module to create a new module. Then copy and paste the following code into the module:
Option Explicit
Sub SplitAtFirstNumber()
Dim selRng As Range, c As Range
Dim sText As String
Dim i As Long, firstPos As Long
Dim title As String
title = "KutoolsforExcel"
' Let user choose the range (safe-cancel)
On Error Resume Next
Set selRng = Application.InputBox( _
Prompt:="Select range to split", _
Title:=title, _
Default:=Selection.Address, _
Type:=8)
On Error GoTo 0
If selRng Is Nothing Then
MsgBox "Operation cancelled.", vbInformation
Exit Sub
End If
Application.ScreenUpdating = False
For Each c In selRng.Cells
If Not IsError(c.Value) And Len(c.Value) > 0 Then
sText = CStr(c.Value)
firstPos = 0
' Find the position of the first ASCII digit 0-9
For i = 1 To Len(sText)
If Mid$(sText, i, 1) Like "[0-9]" Then
firstPos = i
Exit For
End If
Next i
If firstPos > 0 Then
c.Offset(0, 1).Value = Left$(sText, firstPos - 1) ' text before the first number
c.Offset(0, 2).Value = Mid$(sText, firstPos) ' remainder from the first number
Else
c.Offset(0, 1).Value = sText
c.Offset(0, 2).Value = ""
End If
Else
' Blank or error cells: copy to text part, keep number part blank
c.Offset(0, 1).Value = c.Value
c.Offset(0, 2).Value = ""
End If
Next c
Application.ScreenUpdating = True
MsgBox "Split completed.", vbInformation
End Sub
2. To run the macro, press F5 in the VBA window, or in Excel press Alt + F8, choose SplitAtFirstNumber, then click Run. A dialog titled “KutoolsforExcel” will prompt you to select the cell range to process; choose your cells and confirm. The macro will split each selected cell—placing the text portion before the first number in the column immediately to the right, and the remainder (number and following characters) in the next column. If no number is found, all data is written to the text column and the number column is left blank. 
Troubleshooting and tips
- Does not overwrite originals: Results are written to the next two columns (
Offset(0,1)andOffset(0,2)). Move/backup data in those columns beforehand if needed, or change the offsets in code. - Character set: The pattern matches ASCII digits
[0-9]. For full-width/non-ASCII numerals, extend the check or normalize input first. - Performance: For very large ranges, this loop is generally fast. If needed, you can read the range to an array and write back once to further speed up.
- Enable macros: Ensure macros are enabled; otherwise the code will not run.
- Undo: VBA actions are not stepwise undoable. Consider saving a copy before running.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
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