Excel Split Alphanumeric String: 4 Ways

To split an alphanumeric string in Excel, you separate the text characters from the number characters within a single cell. You can do this using Flash Fill, Text to Columns, formulas, or Power Query, and the best method depends on whether your numbers are always at the start, always at the end, or mixed throughout.

Formula

The formula above extracts letters and numbers from a mixed string. In words: it checks each character in the cell, keeps it if it is a number (for the numbers output) or discards it if it is a number (for the letters output), then joins the kept characters together.

Variables:

• t – the text string you want to split (e.g., “A1B2C3”).
• MID(t, position, 1) – extracts one character from the string at a given position.
• ROW(INDIRECT(“1:”&LEN(t))) – creates a sequence of numbers from 1 to the length of the string, so each character is checked.
• ISNUMBER(–MID(…)) – converts the character to a number (if possible) and checks if it is numeric.
• CONCAT(…) – joins the resulting characters into one string.

This is an array formula. In older Excel versions, you must press Ctrl+Shift+Enter; in Excel 365 and Excel 2021, it works with a normal Enter.

Worked Example

Using the default text A1B2C3:

1. Length of string = 6, so the sequence is {1,2,3,4,5,6}.
2. Character at position 1 = “A” → not a number → keep for letters, drop for numbers.
3. Character at position 2 = “1” → is a number → keep for numbers, drop for letters.
4. Continue for all characters: A, 1, B, 2, C, 3.
5. Letters output = “ABC” (A + B + C).
6. Numbers output = “123” (1 + 2 + 3).

So the formula returns ABC for letters and 123 for numbers, exactly as the calculator shows.

Why You Might Need to Split Alphanumeric Strings

Spreadsheets often contain combined codes like product IDs (“SKU123”), invoice numbers (“INV2024”), or part numbers (“AB-12CD”). You may need to split these because:

• You want to sort by the numeric portion (e.g., sort by the number after “INV”).
• You need to compare only the text part with another list.
• You want to extract the numeric part to perform calculations.
• You need to clean data for import into another system that requires separate fields.

Without splitting, such operations are difficult because Excel treats the whole cell as text.

Method 1: Flash Fill (Easiest for One-Time Splits)

Flash Fill (Excel 2013 and later) recognizes patterns in your data. To use it:

1. In a new column, type the letters part for the first cell (e.g., if cell A1 is “A1B2C3”, type “ABC”).
2. Start typing the letters part for the second cell (e.g., if A2 is “X9Y8”, type “XY”).
3. Excel will suggest a fill; press Enter to accept.

Repeat for the numbers column. Flash Fill is quick but not dynamic – if your data changes, you must redo it.

Method 2: Text to Columns (When Numbers Are Always at the Start or End)

If your strings have a consistent pattern – numbers always first (e.g., “123ABC”) or always last (e.g., “ABC123”) – you can use Text to Columns:

1. Select your data column.
2. Go to Data > Text to Columns.
3. Choose Fixed width (not Delimited) and click Next.
4. In the preview, click where the number part ends (or begins) to create a break line.
5. Finish. The column will split into two columns.

This method is manual and only works if the break point is the same for all rows. For variable-length numbers, use formulas instead.

Method 3: Formulas (Dynamic and Flexible)

For a dynamic solution that updates when your data changes, use formulas. The array formula shown above works for any mix of letters and numbers. If you prefer a non-array approach, you can use SUBSTITUTE and MID in a loop, but that gets complex. A simpler alternative for common cases:

• Numbers at the end: =LEFT(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789"))-1) gives the text part; the number part is =RIGHT(A1, LEN(A1)-LEN(text_part)).
• Numbers at the start: use RIGHT and LEFT similarly.

These formulas are easier to understand but only work when numbers are consistently at one end.

Method 4: Power Query (Best for Large Datasets)

Power Query (Get & Transform) is ideal when you need to split hundreds or thousands of rows repeatedly. Steps:

1. Select your data, go to Data > From Table/Range (create a table if needed).
2. In Power Query, add a custom column with a formula like = Text.Select([Column], {"A".."Z"}) for letters, and = Text.Select([Column], {"0".."9"}) for numbers.
3. Close & Load to a new worksheet.

This method is fast, repeatable, and doesn’t require complex formulas.

Common Mistakes and Pitfalls

• Forgetting to press Ctrl+Shift+Enter in older Excel – the formula returns only the first character.
• Assuming numbers are contiguous – the array formula above extracts all digits, even if they are separated by letters (e.g., “A1B2” gives “12”). If you need the number as a single block, use a different approach.
• Locale differences – in some regions, the list separator is a semicolon (;) instead of a comma. Adjust formulas accordingly.
• Leading zeros – if the numeric part has leading zeros (e.g., “A001”), the formula returns “001” as text. If you convert to number, the zeros disappear.
• Non-ASCII characters – the array formula only works with standard letters and digits. For accented characters, use UNICODE checks.

How to Choose the Right Method

Consider your situation:

• One-time cleanup – Flash Fill or Text to Columns.
• Data changes regularly – formulas (dynamic).
• Large dataset, recurring task – Power Query.
• Numbers always at start or end – simple LEFT/RIGHT formulas.
• Mixed positions – array formula or Power Query.

For most users, the array formula is the most versatile, but it can be slow on very large datasets (over 10,000 rows). In that case, Power Query is faster.

Frequently Asked Questions

How do I split a string like “ABC123DEF” into letters and numbers?

The array formula above will give “ABCDEF” for letters and “123” for numbers. If you want to split into three columns (ABC, 123, DEF), you need a more complex formula that finds the positions of the first and last digit.

Why does my formula return #VALUE! error?

This usually happens because you didn’t enter it as an array formula (Ctrl+Shift+Enter) in older Excel versions, or because the string contains characters that cannot be converted to a number (like a hyphen). Check for non-breaking spaces or other hidden characters.

Can I split a string with decimal numbers like “A1.5B”?

Yes, but the simple array formula will treat the decimal point as a non-number and remove it, giving “15” not “1.5”. To keep the decimal, you need a more advanced formula that checks for periods as well.

Does Flash Fill work on all Excel versions?

Flash Fill is available in Excel 2013 and later. If you have an older version, you’ll need to use formulas or Text to Columns.

How do I split a string where numbers are always at the beginning, like “123ABC”?

Use =LEFT(A1, LEN(A1)-COUNT(FIND({0,1,2,3,4,5,6,7,8,9}, A1))) for text, but this only works if the number part is the same length in all rows. A more reliable method is the array formula, which handles any position.

Can I split a column of alphanumeric strings without using formulas?

Yes, you can use Power Query (Get & Transform) which provides a point-and-click interface. It’s especially useful for large datasets and can be refreshed when your source data changes.

Related

• Normalize Size Units to Gigabytes?
• Vivo V70 FE vs V70: Bigger Screen or Telephoto Zoom?
• Kingdom Hearts Games in Release Order: Complete Guide

in t | Text string | - | A1B2C3 | 0
out letters | Letters only | - | CONCAT(IF(ISNUMBER(--MID(t,ROW(INDIRECT("1:"&LEN(t))),1)),"",MID(t,ROW(INDIRECT("1:"&LEN(t))),1))) | 0
out numbers | Numbers only | - | CONCAT(IF(ISNUMBER(--MID(t,ROW(INDIRECT("1:"&LEN(t))),1)),MID(t,ROW(INDIRECT("1:"&LEN(t))),1),"")) | 0