How to Separate Numbers from Text in Excel: Easy Guide

Sharing is caring!

Excel is an incredibly versatile tool that enables users to perform various data manipulation tasks. One common challenge is separating numbers from text when they are combined in a single cell. For example, you might encounter data like “ABC123” and need to extract “ABC” as text and “123” as numbers.

In this guide, we will walk you through various techniques to separate numbers from text using Excel formulas, Flash Fill, Power Query, and VBA.

Why Separate Numbers from Text in Excel?

When working with datasets that combine text and numbers, it is often essential to split these elements for different purposes:

1. Data Analysis: Isolating numerical values helps in calculations and generating reports.
2. Standardization: Extracting text ensures consistency in names, product labels, or identifiers.
3. Automation: Splitting mixed data makes it easier to work with formulas, pivot tables, or scripts.

Some common examples include:

• Product IDs: “ABC123” -> Extract “ABC” (text) and “123” (numbers)
• Room numbers: “Room25” -> Separate “Room” (text) and “25” (numbers)
• Alphanumeric codes: “B12A34” -> Extract “BA” (text) and “1234” (numbers)

Understanding how to clean and format your data efficiently saves time and ensures accuracy.

Methods to Separate Numbers from Text

Let’s explore four primary methods for separating numbers from text in Excel:

1. Using Formulas
2. Using Flash Fill
3. Using Power Query
4. Using VBA (Visual Basic for Applications)

1. Using Formulas to Separate Numbers from Text

Excel offers several built-in functions that you can combine to separate numbers and text. While formulas can handle complex patterns, they require some understanding of Excel’s functions.

Extracting Text from a Mixed Cell

To extract only the text portion from a cell, use this array formula:

=TEXTJOIN("", TRUE, IF(ISERR(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)+0), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""))

Explanation:

1. MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) checks each character in the cell.
2. ISERR(...+0) identifies non-numeric characters.
3. TEXTJOIN combines the extracted text into a single string.

Extracting Numbers from a Mixed Cell

To extract only the numbers from a cell, use this array formula:

=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)+0), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""))

Explanation:

1. MID extracts each character from the cell.
2. ISNUMBER identifies numeric characters.
3. TEXTJOIN concatenates the numbers into a single string.

Alternative for Structured Patterns

If your data always has text followed by numbers, use simpler formulas like:

• Text: =LEFT(A1, FIND("1", A1)-1)
• Numbers: =RIGHT(A1, LEN(A1)-LEN(LEFT(A1, FIND("1", A1)-1)))

Example Table

Formulas provide flexibility but can become complex with irregular patterns.

2. Using Flash Fill

Flash Fill is a user-friendly and quick method to separate text and numbers in Excel. It works by identifying patterns based on the example you provide.

Steps to Use Flash Fill:

1. Input Data: Start with a column containing mixed data.
2. Provide Example:
   • In a new column, manually type the text portion for the first cell (e.g., type “ABC” for “ABC123”).
   • Repeat this process for the numbers column (e.g., type “123” for “ABC123”).
3. Apply Flash Fill:
   • Select the column with your example.
   • Press Ctrl + E or go to the Data tab and click Flash Fill.
4. Review Output: Flash Fill will automatically populate the remaining rows based on the detected pattern.

Advantages:

• Requires no complex formulas or coding.
• Easy to use for small datasets.

Limitations:

• Flash Fill relies on patterns and may fail with irregular data formats.

3. Using Power Query

Power Query is an advanced tool for data cleaning and transformation. It’s ideal for handling large datasets and complex transformations.

Steps to Separate Numbers and Text Using Power Query

1. Load Data into Power Query:
   • Select your dataset.
   • Go to the Data tab and choose Get & Transform Data > From Table/Range.
2. Split Columns:
   • In Power Query, select the column with mixed data.
   • Right-click and choose Split Column > By Non-Digit to Digit.
3. Refine and Rename Columns:
   • Power Query will create separate columns for text and numbers. Rename these columns as needed.
4. Close & Load:
   • Click Close & Load to return the transformed data to your Excel worksheet.

Advantages:

• Handles large datasets efficiently.
• Provides a robust solution for repetitive tasks.

Example Table:

4. Using VBA (Visual Basic for Applications)

For advanced users, VBA is a powerful tool to automate the process of separating numbers from text. It’s especially useful if you frequently need to split mixed data.

VBA Code to Separate Numbers and Text

Sub SplitTextAndNumbers()
    Dim rng As Range
    Dim cell As Range
    Dim i As Integer
    Dim txt As String
    Dim num As String

    Set rng = Selection

    For Each cell In rng
        txt = ""
        num = ""
        For i = 1 To Len(cell.Value)
            If IsNumeric(Mid(cell.Value, i, 1)) Then
                num = num & Mid(cell.Value, i, 1)
            Else
                txt = txt & Mid(cell.Value, i, 1)
            End If
        Next i
        cell.Offset(0, 1).Value = txt
        cell.Offset(0, 2).Value = num
    Next cell
End Sub

Steps to Use VBA:

1. Open the VBA Editor by pressing Alt + F11.
2. Insert a new module and paste the code above.
3. Close the VBA editor and return to Excel.
4. Select the range containing mixed data and run the macro by pressing Alt + F8.

Output: The script will split text and numbers into separate columns.

Advantages:

• Fully automated solution.
• Ideal for repetitive tasks or irregular patterns.

Best Practices for Separating Numbers and Text in Excel

• Validate Data: Always review your results to ensure accuracy, especially for large datasets.
• Choose the Right Method: Use Flash Fill for quick tasks, Power Query for large datasets, and VBA for automation.
• Standardize Formats: Consistent data formatting improves the accuracy of separation methods.

Summary of Methods to Separate Numbers from Text in Excel

Final Thoughts

Separating numbers from text in Excel is an essential skill for efficient data management and analysis. Depending on your dataset size, complexity, and skill level, you can choose from formulas, Flash Fill, Power Query, or VBA to extract text and numbers. By mastering these techniques, you can save time, improve data accuracy, and enhance your productivity.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.