Yes. With mixed values such as AB-123 in A2, Excel for Microsoft 365 can extract the digits with a formula that joins every digit character it finds. A companion formula can return the non-digit characters, while simpler formulas work well when the text and number are separated by a consistent delimiter.
What kind of split do you need?
“Split numbers and text” can mean several different things. A cell might contain a label followed by a number, such as “Order 4821”; letters followed immediately by digits, such as “AB123”; or a value with punctuation and digits mixed throughout, such as “Room 4B.” The best formula depends on which pattern your data follows.
First, decide what should count as the number. Extracting digits from AB-123 returns 123 as text. Extracting digits from 12.50 returns 1250, because the period is not a digit. Likewise, a minus sign is not a digit, so -25 would produce 25. These formulas separate characters; they do not automatically understand every character sequence as a mathematical number.
- Known separator: The parts are divided by a space, hyphen, comma, or another consistent character.
- Letters and digits together: The value has no reliable separator, so the formula must test each character.
- Several fields: The cell contains multiple items separated by a repeated delimiter.
- Irregular records: The values need cleanup or rules beyond extracting characters.
Extract digits from a mixed value
With the source value in A2, this Microsoft 365 formula examines each character, keeps characters that can be converted to numbers, and joins them into one result:
=IF(A2="","",LET(s,A2,c,MID(s,SEQUENCE(LEN(s)),1),TEXTJOIN("",TRUE,FILTER(c,ISNUMBER(IFERROR(--c,"")),""))))
For A2 containing AB-123, the result is 123. For Room 4B, the result is 4. The result is text, even when it looks like a number; that is useful when the digits are an ID or must retain leading zeroes. The formula checks individual characters, so a decimal point, comma, plus sign, and minus sign are not included.
LET gives names to the source string and the array of individual characters. SEQUENCE and MID make that character array, while FILTER keeps the characters that pass the numeric test. TEXTJOIN combines the survivors without adding a separator. The IFERROR test treats characters that cannot be converted, such as letters, as nonnumeric instead of letting them cause an error. The outer IF returns a blank for a blank source cell.
With the extracted digit string in B2, use this formula in C2 if you need a numeric value for arithmetic:
=IF(B2="","",VALUE(B2))
For the example AB-123, C2 returns the number 123. Converting to a number removes leading zeroes: the text 007 becomes numeric 7. Keep the text result when those zeroes are meaningful, as they often are in product codes, postal codes, and account identifiers.
Return the text and other non-digit characters
With the original mixed value in A2, this companion formula returns every character that is not a digit:
=IF(A2="","",LET(s,A2,c,MID(s,SEQUENCE(LEN(s)),1),TEXTJOIN("",TRUE,FILTER(c,NOT(ISNUMBER(IFERROR(--c,""))),""))))
For AB-123, the result is AB-. For Room 4B, it is Room B. Notice that this formula preserves spaces and punctuation as well as letters: it means “non-digit characters,” not strictly “letters.” That distinction matters if you later compare the result with another text value or use it as a label. If you want to remove unwanted punctuation or spaces, add a separate cleanup step suited to your data rather than assuming this formula removes them.
The digit and non-digit formulas can be placed in separate columns and filled down for a list. In Microsoft 365, click the formula cell and drag or double-click its fill handle to copy it down alongside the source records. The cell reference A2 adjusts to A3, A4, and so on. Check several representative rows before relying on the results, especially rows with blank cells, unusual symbols, or multiple groups of digits.
Split at a known delimiter
If the data follows a stable pattern, a delimiter-based formula is usually shorter and easier to maintain. Suppose A2 contains Order 4821, with one space between the label and the number. With that layout, this formula returns the text before the first space:
=TEXTBEFORE(A2," ")
With the same A2 layout, this formula returns the text after the first space:
=TEXTAFTER(A2," ")
The results are Order and 4821, respectively. TEXTAFTER returns text, so use a conversion formula if the second part must be numeric. These functions are available in Microsoft 365 and Excel 2024. They are convenient when there is a clear separator, but they do not solve a value like AB123 because no delimiter marks where the letters stop and the digits begin.
For a workbook that must also work in older Excel versions, with A2 still containing Order 4821, use the following LEFT formula for the part before the first space:
=LEFT(A2,FIND(" ",A2)-1)
With the same cell layout, this MID formula returns everything after the first space:
=MID(A2,FIND(" ",A2)+1,LEN(A2))
These older formulas rely on FIND locating a space. If the expected space is missing, FIND returns an error. They also split at the first matching space only, so a label containing multiple words may need a different delimiter or a more specific rule. If some records use a hyphen and others use a space, standardize the source data or handle the variations deliberately.
Choose a formula that matches the pattern
Before building a formula, inspect real examples from the beginning, middle, and end of the dataset. A column that appears regular at first may include exceptions such as blank values, extra spaces, or codes that contain digits in the label. Choose the simplest method that correctly handles those cases.
- Use TEXTBEFORE and TEXTAFTER when a consistent delimiter clearly separates the desired parts and your Excel version supports those functions.
- Use LEFT, MID, FIND, or RIGHT for fixed structures or compatibility with older workbooks. These functions are widely available, but their formulas are less forgiving when the pattern changes.
- Use character-by-character extraction when digits and text are joined without a consistent delimiter and your Excel version supports the required dynamic-array functions.
- Use Power Query when you repeatedly import and clean a large dataset, need several transformation steps, or want a refreshable process instead of formulas in the worksheet.
- Use Flash Fill for a one-time pattern that Excel can recognize from examples. It creates values rather than a formula-driven result, so later changes to the source may not update the filled output.
For the character-by-character examples above, LET, FILTER, and SEQUENCE require Microsoft 365 or Excel 2021 or later. TEXTBEFORE and TEXTAFTER require Microsoft 365 or Excel 2024. If you share a workbook, the recipient’s Excel version matters: a formula using a function unavailable in their version may display a function-name error. For a workbook used across mixed versions, test it in the oldest version that must support it.
Common problems and how to fix them
The extracted number is text. TEXTJOIN returns text, which is appropriate for codes but not always for calculations. Convert it with VALUE only when the result is a genuine number and leading zeroes are not important. If conversion returns an error, inspect the extracted string for punctuation or spaces.
Decimals or negative values do not come out as expected. The digit-extraction formula deliberately selects the characters 0 through 9; it does not preserve decimal separators or a leading minus sign. If your source contains actual numeric values formatted to look mixed with text, a different approach may be needed. Decide whether the decimal mark is a period or comma in your data, and define how signs and thousands separators should be treated before designing a formula.
A delimiter formula returns an error. FIND returns an error when it cannot locate the requested character. Check for inconsistent separators or invisible differences such as a nonbreaking space. If some records have no delimiter, decide what result those rows should produce and add error handling only after defining that rule.
The formula shows #NAME? or an unrecognized function. The workbook may be open in an Excel version that does not support a function such as FILTER or TEXTBEFORE. Confirm the version in use and choose a compatible formula. Also check that the function name and quotation marks were copied correctly.
Excel rejects the commas. The examples use commas as argument separators. Some regional settings use semicolons instead. If Excel expects semicolons, replace argument-separating commas with semicolons; do not change commas that are part of quoted text. Formula names may also be localized in some language editions.
Spaces remain in the text result. The non-digit formula preserves all non-digit characters, including spaces. If unwanted leading or trailing spaces are the only issue, TRIM can clean ordinary spaces around the result; it does not remove every kind of invisible whitespace. Avoid removing internal spaces if they separate words that should remain together.
Formulas compared with Flash Fill and Power Query
Formulas are best when results should update automatically as source cells change, or when you want the extraction logic visible in the worksheet. They also make it possible to use the extracted value in other calculations. Their main limitation is that the formula must encode the pattern accurately. A formula that silently returns the wrong characters can be more troublesome than one that produces an obvious error, so validate the results against the source.
Flash Fill is useful when the transformation is simple and you can provide clear examples in an adjacent column. In Excel for Microsoft 365 on Windows, enter one or two desired results, then use Data > Flash Fill or press Ctrl+E. On Mac, the shortcut is Control+E; the command is available from the Data tab in supported Excel versions. Flash Fill is not a live formula: if the source changes later, you may need to run it again.
Power Query is often a better fit for recurring imports and multi-step cleanup. In Excel for Microsoft 365 on Windows, start from Data > Get Data or use the available commands in the Get & Transform Data group, then split or transform the column in the Power Query Editor. The exact available connectors and commands can vary by Excel platform and update. Unlike a worksheet formula, a Power Query transformation is generally refreshed to apply to the imported data; it does not recalculate each row as an ordinary cell formula does.
Frequently Asked Questions
Can Excel split letters and numbers without a space?
Yes. In Microsoft 365 or Excel 2021 and later, use a character-by-character formula such as the digit-extraction formula above. It does not need a space or other delimiter, but it treats every nondigit character as text and does not infer the meaning of punctuation.
How do I split text and numbers into separate columns?
Put the source values in one column, then enter a digit-extraction formula in one adjacent column and a non-digit formula in another. Fill both formulas down. Check whether you want the digits kept as text or converted to numbers before using them in calculations.
Can I keep leading zeroes in an extracted number?
Yes. Keep the extraction result as text; for example, a code containing 007 remains 007. Converting that result with VALUE makes it numeric and changes it to 7. For identifiers, preserving the text form is usually the safer choice.
Why does extracting digits from 12.50 return 1250?
The formula selects digit characters and ignores the decimal point, so it joins 1, 2, 5, and 0 as 1250. It is not a general-purpose parser for signed or decimal numbers. Use a formula designed around the precise numeric format if the decimal point or sign must be retained.
Do these formulas work in Excel for Mac or Excel for the web?
Modern Excel for Mac and Excel for the web support many of the same worksheet functions, but availability can depend on the version and update channel. The character-extraction formula needs LET, FILTER, and SEQUENCE. Check function support in the Excel version used by everyone who needs to open the workbook.
Related
- Project Contact List Excel Template: Build and Use It
- Project Plan Timeline Template Excel: Build One That Works
- Real Estate Profit and Loss Excel Template: A Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Cell Value From List: Find or Select a MatchTo return a value from a list when you know a matching item, use XLOOKUP; with product IDs…
- Excel ISBLANK Formula Examples and Common MistakesExcel’s ISBLANK formula checks whether a cell is truly empty: enter =ISBLANK(A2) to get TRUE if A2 has…
- Concatenate Excel Formula With Comma: ExamplesTo concatenate two cells with a comma and a space between them, use =A2&", "&B2. For example, if…
- Excel Filter Shortcut: Turn AutoFilter On or OffIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn the filter buttons on or off. The…
- Can Excel Table Headers Use Formulas? What to DoNo. In Excel for Microsoft 365 on Windows, a table header is a column label, not a worksheet…
- Excel Remove Duplicates: How to Keep the Right RowsTo remove duplicates in Microsoft Excel for Microsoft 365 on Windows, select your data, then choose Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.