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