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**

*THE FIRST NUMBER*

- 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.

## Leave a Comment