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

Data Analysis

  • Conditional Formatting Data bars Examples in Excel
  • How to create Gauge Chart in Excel
  • How To Compare Two Lists in Excel
  • Move chart to a separate worksheet in Excel
  • How to add Trendline to a chart in Excel

References

  • Extract data with helper column in Excel
  • How to get first row number in range in Excel
  • Lookup entire row in Excel
  • Create hyperlink with VLOOKUP in Excel
  • How to use Excel LOOKUP Function

Data Validations

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

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:

Worked Example:   How to get sheet name only in Excel
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.

Worked Example:   Excel Data validation must contain specific 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.

Worked Example:   Minimum value in Excel

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:

Put names into proper case — Manipulating NAMES in Excel

Next Post:

Create One-dimensional and Two-dimensional Array

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

  • Extract multiple matches into separate rows in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • SWITCH function example in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to Calculate Age in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Get month from date in Excel
  • How to calculate workdays per month in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • If cell contains one of many things in Excel
  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign data to groups in Excel
  • Categorize text with keywords in Excel

General

  • How to calculate percent sold in Excel
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • How to calculate project complete percentage in Excel
  • Using Existing Templates in Excel
  • Delete Blank Rows at Once in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning