To split letters and digits from the same Excel cell, put the mixed value in A2, then use a formula in another cell to collect its digits and a second formula to collect its non-digit characters. For example, these formulas can turn SKU-204B into 204 and SKU-B. If your entries use a consistent separator, Text to Columns may be quicker; if you have a few rows, Flash Fill is another option.
What You’ll Need
- A copy of your workbook or an empty pair of columns for the results. Keep the original data until you have checked the split.
- A consistent example of the mixed values you want to separate. The formula method below works when digits and text are mixed in any order, but it treats each character separately.
- Microsoft 365 or Excel 2024 for the formulas using
SEQUENCE. If you have an older Excel version, use Flash Fill or Text to Columns, or ask for a formula suited to that version.
Step-by-Step
- Check what the cell contains. Select a few source cells and note whether the digits always come at the end, whether the text and digits have a delimiter such as a hyphen, and whether the numbers include decimal points, signs, or leading zeros. Those details affect which method is safest. In this guide, the general formula example uses
SKU-204B: it extracts the digit characters as204and keeps the other characters asSKU-B. - Make room for two results. Keep the original values in column A and add headings such as Text in B1 and Digits in C1. Put the first source value in A2. Using separate output columns means you can compare the results against the source before replacing or deleting anything.
- Extract the digits with a formula. With the mixed value in A2, enter this in C2. It examines each character and joins the characters that can be read as individual digits. For
SKU-204B, it returns204.
With the source value in A2, enter this formula in C2:
=IF(A2="","",TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")))
- Extract the text and other non-digit characters. With the same source value in A2, enter the following in B2. It keeps each character that is not a digit, including spaces and punctuation. For
SKU-204B, the result isSKU-B; the hyphen is retained because it is not a digit.
With the source value in A2, enter this formula in B2:
=IF(A2="","",TEXTJOIN("",TRUE,IF(IFERROR(ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)),FALSE),"",MID(A2,SEQUENCE(LEN(A2)),1))))
- Check both outputs before filling down. Compare B2 and C2 with A2. Confirm that the digit result contains exactly the digits you expect, and that the text result has not lost a needed space, hyphen, or letter. The formulas return text strings, even when the digits look like a number. This can be useful for product codes and values with leading zeros.
- Copy the formulas to the remaining rows. Select B2:C2 and drag the small square at the lower-right corner of the selection down to the last data row. You can also select the range to fill and press Ctrl+D on Windows. Excel adjusts the references for each row, so the next row uses A3, then A4, and so on. On Mac, use the fill handle or the Fill Down command on the Home tab; keyboard shortcuts can differ by keyboard settings.
- Review unusual values and blanks. Check rows containing no digits, digits only, or an empty source cell. The formulas return an empty digit result when there are no digits and an empty text result when there is no text. If the source cell is blank, both formulas return blank. Check punctuation carefully: the general method separates digit characters, not mathematical number formats.
- Make the results permanent if needed. Once you have verified the outputs, you can leave the formulas in place or copy the result columns and use Home > Paste > Paste Values to replace them with fixed results. Keep a copy of the original column until you are certain that the split is correct.
Alternative for a known separator: If the parts are divided by a consistent character, such as a hyphen, you may not need to extract characters one at a time. With the full value in A2 and a hyphen separating the parts, you can use Excel’s Text to Columns tool. Select the source cells, choose Data > Text to Columns, select Delimited, choose Next, check the delimiter you use, and inspect the preview. Choose a destination cell that will not overwrite data, then select Finish. This works for a real separator; it will not automatically separate a value such as SKU204B just because letters and numbers touch.
Alternative for a short list: Flash Fill can recognize an example you type. With SKU-204B in A2, type the desired text result SKU-B in B2, then select B3 and choose Data > Flash Fill. Repeat in the digits column by typing 204 beside the first source value and invoking Flash Fill. On Windows, Ctrl+E runs Flash Fill; on Mac, use Data > Flash Fill if the command is available in your Excel version. Inspect every result, especially when the patterns vary, because Flash Fill copies a pattern rather than applying a formula rule.
Common Mistakes
Expecting the digit formula to understand a whole number. The formula collects digit characters, not a numeric value. For example, a source such as -12.5kg can produce 125, because the minus sign and decimal point are not digits. Do not use this method unchanged when signs, decimal separators, or thousands separators are meaningful.
Overwriting the source column. Text to Columns can put its results beside the source, but choosing a destination that overlaps existing data can replace it. Use a blank area, and keep an original copy until you have reviewed the split.
Dropping leading zeros by converting the result to a number. A code like 007 is often an identifier, not a quantity. The formula returns a text string, so it can preserve those zeros. Converting that result to a number would display it as 7.
Assuming every non-digit is a letter. The text formula also retains spaces, hyphens, punctuation, and symbols. That is usually useful, but you may need to clean the result if those characters are unwanted. Conversely, do not remove them blindly if they distinguish valid codes.
Using the general formula on a version without SEQUENCE. Excel versions that do not support SEQUENCE may show a function error or fail to calculate the formula. Use Flash Fill or Text to Columns instead, or use a formula designed for your Excel version.
Tips
Start by testing a few representative rows, not only the easiest example. Include the shortest and longest values, entries with text before and after the digits, and any unusual punctuation. This helps reveal whether a simple delimiter-based split or the character-by-character formulas are more appropriate.
If your data always follows a structure such as letters followed by digits, preserving that structure can be clearer than collecting every digit anywhere in the cell. For example, values with a consistent separator are good candidates for Text to Columns. If the digits can appear in different positions, the formulas above collect all digit characters regardless of position.
To keep formulas easy to audit, label the output columns and leave the source column visible while reviewing. Consider formatting the output columns as Text before entering values if the results are codes with leading zeros. After copying formulas down, use filters or sort the source and output columns together to find blanks or unexpected results; do not sort only one column, or rows may no longer match.
The formulas use MID, SEQUENCE, and TEXTJOIN. They are suitable for Microsoft 365 and Excel 2024. The same formula approach is available in current Microsoft 365 Excel for Mac; ribbon locations can vary slightly. In Excel for the web, formula availability may depend on the current update, so check that the formula calculates before filling a large range.
Troubleshooting
The formula shows #NAME? Your Excel version may not recognize SEQUENCE, or a function name may have been mistyped. Confirm that you are using Microsoft 365 or Excel 2024 and that the formula was pasted with its opening equals sign. If the function is unavailable, use Flash Fill or Text to Columns for data that follows a repeatable pattern.
The formula is displayed instead of calculated. Check that the cell is not formatted as Text. Change its format to General, select the cell, press F2, and then press Enter to make Excel recalculate it. Also check that the formula begins with = and does not have an apostrophe before it.
The result omits a minus sign or decimal point. That is expected: the digit formula collects only individual digits. If your values represent signed or decimal quantities, do not interpret its output as the original number. Use a consistent delimiter or define explicit rules for the number format, then test those rules on negative and decimal examples before applying them to the full list.
The text result contains extra symbols or spaces. The non-digit formula intentionally keeps every character that is not a digit. Review whether the symbols are part of the code. If you need to remove particular characters, do that as a separate, deliberate cleanup rather than silently deleting punctuation that may carry meaning.
Flash Fill produces inconsistent results. Give it more than one example that shows the intended pattern, then run it again and inspect the whole output. If entries follow several different patterns, Flash Fill may infer the wrong rule. Use a formula or split the data into pattern groups instead.
Frequently Asked Questions
Can Excel split letters and numbers automatically?
Yes. Flash Fill can infer a split from examples, and formulas can separate digit characters from other characters. For dependable results on a changing list, formulas are easier to recalculate and review.
Can Text to Columns split text from numbers when they touch?
Not by itself. Text to Columns splits at a delimiter, such as a comma or hyphen, or at a fixed character position. If letters and numbers touch with no consistent boundary character, use the formulas or Flash Fill.
Will the digit formula return a real number?
No. It returns a text string made from digit characters. That preserves leading zeros, which is often right for codes. If you need to calculate with a simple integer, convert only after checking that the value has no meaningful leading zeros, signs, or decimal points.
What if my cell contains several groups of digits?
The digit formula joins all digit characters into one result. A value such as AB12-CD34 becomes 1234, while the text output is AB-CD. If you need to preserve separate number groups, split at a known delimiter or create a rule for each group.
Can I split numbers that include decimals or negative signs?
Not accurately with the digit-only formula, because punctuation is not collected as part of the number. Use a consistent separator or a formula designed around your exact number format, and verify results for negative values, decimals, and regional decimal separators.
Do these instructions work in Excel for Mac?
The formulas work in Microsoft 365 for Mac when the required functions are available. Ribbon labels and shortcuts can differ from Windows; use the Mac ribbon command for Flash Fill if available, or use the fill handle to copy formulas down.
Related
- Clean Excel Duplicates: Find, Review, and Remove Them
- Clean Excel Data With Power Automate: A Practical Guide
- Clean Excel Columns: Remove Spaces, Duplicates, and Errors
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel vs SharePoint Lists: Which Should You Use?Use Excel when you need flexible calculations, analysis, or charts; use a SharePoint list when people need to…
- Clean Excel Columns: Remove Spaces, Duplicates, and ErrorsTo clean an Excel column, first keep an untouched copy of the original data, then remove extra spaces…
- Sort Highest to Lowest in Google Sheets: A Clear GuideTo sort values from highest to lowest in Google Sheets, select the full data range, choose Data >…
- Copy Excel to Word Without Gridlines: Easy MethodsTo copy an Excel range into Word without visible gridlines, select the cells, turn off View > Gridlines…
- Google Sheets Checkbox IF Statements: Examples & TipsIn Google Sheets, use an IF formula to return one result when a checkbox is checked and another…
- Excel Not Inserting Columns? Common Causes and FixesIf Excel will not insert a column, first check whether the worksheet is protected, whether cells or formatting…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.