How to Split Text and Numbers with a Formula in Excel

We can easily split text and numbers from a cell by using different combinations of functions such as FIND, MIN, LEN, LEFT, and RIGHT functions. The steps below will walk through the process of splitting text and numbers contained in a Cell.

Figure 1: Split text and Numbers with a Formula

Setting up the Data

We will set up the data by inserting the Names of five college students in Column A, including their date of birth without leaving a space between the name and the date of birth.

We will also input Position, Name, and Date of Birth into Cell B3, Cell C3, and Cell D3 respectively.

Figure 2: Setting up the Data

The Basic Formula

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

• We can use the basic formula to split text and numbers from a cell in accordance with the LEFT or RIGHT function as per the requirements.

Determining the Position of THE FIRST NUMBER in Cell A4 to Cell A8

• We will insert the formula below into Cell B4

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

Figure 3: Position of the First Number

• Now, we will press enter

Figure 4: Position of the First Number

• As shown in figure 4, this formula returns the position of the first number in Cell A4. JOHN comprises of four letters, the next value is not a text but a number, hence, the position of the first number is 5
• We will use the drop-down feature to get the positions of the first number for the remaining four students

Figure 5: Position of the First Number

Extracting the Text with the LEFT function

• We will use the formula below to extract the text (names) from Column A:

=LEFT(A4,B4-1)

• We will insert this formula into Cell C4 and use the drop-down feature to fill the remaining cells

Figure 6: Split text and Numbers with the LEFT function

Extracting the Numbers with the RIGHT function

• We will use the formula below to extract the numbers (date of birth) from Column A:

=RIGHT(A4,LEN(A4)-B4+1)

• We will insert this formula into Cell D4 and use the drop-down feature to fill the remaining cells

Figure 7: Split text and Numbers with the RIGHT function

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.