< Go Back

How to Split Text and Numbers with a Formula in Excel

You may find it difficult to split numbers and text from a single cell. But using MS Excel formulas, we can get unlimited solutions to our problems. In this article, we are going to explain the formula to split text and numbers from a single cell instantly. You have to use various functions and formulas to do so. Let’s start.

Split Text and Numbers Using Excel Formula

Basic Formula

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

Explanation of the Formula

Based on the FIND, MIN, and LEN function, this formula can be used to split text and numbers from a cell in accordance with the LEFT or RIGHT function as per the requirements.

Example 1.1

In the given example, the formula in D6 is:

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

It returns the position of the number in the given value “George 1995”, 1 i-e, “8”.

Figure 1:  Example 1.1 of the split text and numbers formula using MIN Function that locates the position of the first digit

Overview

The majority of formulas used to split text are based on locating the position of the value which Excel needs to retrieve. Once you get the right position, using other functions, you can extract the desired value.

In our example, we have mentioned names along with their birth years in the same cell. We have then split the name and the birth year into two different cells. For the reference, please check the table below.

Table 1. Example of the Name with birth dates in the same cell and split into two different cells

The formula to Use LEFT Function

Once we get the position, we have to use the following formula to get the text:

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

Example 1.2

In the example, the formula applied in E6 is:

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

See the screenshot below for reference.

Figure 2. Example 1.2 of the extraction of the Name using LEFT function

A formula to Use RIGHT Function

After applying the LEFT function, retrieve the number using RIGHT function.

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

Example 1.3

In the given example, the formula used in F6 is:

=RIGHT(C6,LEN(C6)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},C6&"0123456789"))+1)

Figure 3. Example 1.3 of the extraction of the Birth Year using the RIGHT function

How This Formula Actually Works

We have used the FIND function to get the location of the number’s starting position above. For the find_text, we have used the array constant {0,1,2,3,4,5,6,7,8,9}, so the FIND function search each value in the array constant separately. As there are 10 numbers in the array constant, we’ll get an array with 10 values as a result.

{12,8,14,15,16,11,18,19,20,9}

In the given array, every single number is representing the position of a specific item in the array constant.

In the next step, we get the smallest value in the list by the MIN function that communicates with the first number’s position in the original text. Further, all number positions are retrieved by the FIND function while the MIN function provides us with the position of the first number.

In the FIND function, the odd construction for within_text may wonder you:

C6&”0123456789″

In this part of the formula, all the possible numbers 0-9 are concatenated with the actual text in C6. It is just a smart way to avoid any errors because the FIND function does not return zero if the value is not found.

In our example, the number appears second in the actual text, so it is an ideal case for the MIN function because it forces the smallest of a number that is to be returned. The position of the number in the original text will be returned here.

If there isn’t any number in the original text, it will return a dummy position that is equal to the original text’s length + 1. Using this dummy position, the LEFT part of the formula will return the text while an empty string will be returned in the RIGHT part of the formula.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar