Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Move chart to a separate worksheet in Excel
  • How to create dynamic reference table name in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Add Outline to Data in Excel
  • Example of COUNTIFS with variable table column in Excel

References

  • How to use Excel INDIRECT Function
  • How to use Excel COLUMN Function
  • Perform case-sensitive Lookup in Excel
  • How to get address of named range in Excel
  • How to use Excel MATCH Function

Data Validations

  • Excel Data validation don’t exceed total
  • Data validation must not exist in list
  • Excel Data validation allow uppercase only
  • Excel Data validation specific characters only
  • Excel Data validation no punctuation

Split text and numbers in Excel

by

To separate text and numbers, you can use a formula based on the FIND function, the MIN function, and the LEN function with the LEFT or RIGHT function, depending on whether you want to extract the text or the number.

Formula

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

Explanation

In the example shown, the formula in C5 is:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))

which returns 7, the position of the number 3 in the string “apples30”.

Overview

The formula looks complex, but the mechanics are in fact quite simple.

As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the position, you can use other functions to extract what you need.

In this case, we are assuming that numbers and text are combined, and that the number appears after the text. From the original text, which appears in one cell, you want to split the text and numbers into separate cells, like this:

Original Text Number
Apples30 Apples 30
peaches24 peaches 24
oranges12 oranges 12
peaches0 peaches 0

As stated above, the key in this case is to locate the starting position of the number, which you can do with a formula like this:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

Once you have the position, to extract just the text, use:

=LEFT(A1,position-1)

And, to extract just the number, use:

=RIGHT(A1,LEN(A1)-position+1)

How the formula works

In the first formula above, we are using the FIND function to locate the starting position of the number. For the find_text, we are using the array constant {0,1,2,3,4,5,6,7,8,9}, this causes the FIND function to perform a separate search for each value in the array constant. Since the array constant contains 10 numbers, the result will be an array with 10 values. For example, if original text is “apples30” the resulting array will be:

{8,10,11,7,13,14,15,16,17,18}

Each number in this array represents the position of an item in the array constant inside the original text.

Next the MIN function returns the smallest value in the list, which corresponds to the position in of the first number that appears in the original text. In essence, the FIND function gets all number positions, and MIN gives us the first number position: notice that 7 is the smallest value in the array, which corresponds to the position of the number 3 in original text.

You might be wondering about the odd construction for within_text in the find function:

B5&"0123456789"

This part of the formula concatenates every possible number 0-9 with the original text in B5. Unfortunately, FIND doesn’t return zero when a value isn’t found, so this is just a clever way to avoid errors that could occur when a number isn’t found.

In this example, since we are assuming that the number will always appear second in the original text, it works well because MIN forces only the smallest, or first occurrence, of a number to be returned. As long as a number does appear in the original text, that position will be returned.

If original text doesn’t contain any numbers, a “bogus” position equal to the length of the original text + 1 will be returned. With this bogus position, the LEFT formula above will still return the text and RIGHT formula will return an empty string.

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • OR function Examples in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel OR Function
  • How to use Excel XOR Function

Date Time

  • How to calculate quarter from date in Excel
  • Sum through n months in Excel
  • Get day name from date in Excel
  • Get days between dates ignoring years in Excel
  • Add workdays no weekends in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • How to randomly assign people to groups in Excel
  • Group times into unequal buckets in Excel
  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel

General

  • How to generate random times at specific intervals in Excel
  • How to calculate total from percentage in Excel
  • How to get original number from percent change in Excel
  • Check if multiple cells have same value with case sensitive in Excel
  • Delete Blank Rows at Once in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning