How to Separate Text and Numbers from One Cell Into Two Columns?

Excel is a powerful tool for organizing, storing, and visualizing large volumes of data. Data is entered in a rectangular block(intersection of rows and columns) which we called a cell. This tool can be used to perform different tasks like creating graphs, and analyzing trends, to get insights from the data using functions and formulas. It is primarily used for financial data analysis by accounting professionals but can be used by everyone as it is very easy to use. Let’s see how to separate text and numbers from one cell into two columns.

Separate Text and Numbers from One Cell Into Two Columns

Suppose we have value in a cell that consists of text as well as numbers as shown in the table,

We want to separate the numbers and text from one cell to store it in a cell of different columns, as given below:

We can perform the above task using a Combination of RIGHT(), LEFT(), MIN() and FIND() functions. For the purpose of demonstration, we will use the following string “Romy kumari 1998”. As an example, We will separate the text and numbers. Follow the below steps,

Step 1: Find the position from where the numbers start in Combined Data.

To find the starting position of number data, we will use FIND() and MIN() functions.

FIND() function

It takes three inputs as parameters:

• find_text: the value we want to find.
• within_text: string value from which we need to find the value.
• start_num: starting position from within_text from where value is to be found.

It returns the position of find_text where it matches with the value inside with_in text.

Syntax:

=FIND(find_text, within_text, [start_num])

MIN() function

It takes an array of numbers as input and returns the minimum value from the array.

Syntax:

=MIN(number1, number2, number3…)

To find the starting position of number we will use the following formula,

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

Example

Output

Step 2: Extract text using RIGHT() or LEFT() function

LEFT() function

It extracts a given number of characters from the left side of a supplied text string.

Syntax 

=LEFT(text, position)

RIGHT() function

It extracts a given number of characters from the right side of a supplied text string.

Syntax

=RIGHT(text, position)

Example:

Using the LEFT() function will extract text values from the given string and using the LEFT function we can extract numbers from it.

Output

Therefore, we can separate the text and numbers using a Combination of RIGHT(), LEFT(), MIN(), and FIND() functions.