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

Data Analysis

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How To Insert and Customize Sparklines in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Excel Line Chart
  • Example of COUNTIFS with variable table column in Excel

References

  • Count unique text values with criteria
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to get last row in numeric data in Excel
  • To count total rows in a range in Excel
  • How to use Excel INDIRECT Function

Data Validations

  • Data validation must not exist in list
  • Excel Data validation don’t exceed total
  • Excel Data validation allow weekday only
  • Prevent invalid data entering in specific cells
  • Excel Data validation unique values only

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

  • Not Equal To ‘<>‘ operator in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • Check multiple cells are equal in Excel
  • SWITCH function example in Excel

Date Time

  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get day name from date in Excel
  • Count holidays between two dates in Excel
  • Get age from birthday in Excel

Grouping

  • If cell contains one of many things in Excel
  • Running count group by n size in Excel
  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel
  • Group times into unequal buckets in Excel

General

  • Convert column number to letter in Excel
  • How to calculate decrease by percentage in Excel
  • How to generate random date between two dates in Excel
  • Share Excel data with Word documents
  • How to calculate percentage of total in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning