Separate Text and Numbers from One Cell Into Two Columns

Excel is a tool for organizing, storing, and analyzing large datasets. Data is entered into cells, which are the intersections of rows and columns. Excel supports a wide range of tasks, such as creating graphs, analyzing trends, and gaining insights using functions and formulas.

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.

1.1 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])

1.2 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

2.1 LEFT() function

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

Syntax 

=LEFT(text, position)

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