Excel Formulas to Split Text and Numbers ~ Quick Method!!

Assume that you have a list of values mixed with text and numbers. Now you have to split the text and numbers in Excel. How can you get them quickly? In this chapter, we will show some easy tricks to solve this problem. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula:

If you want to split the text and numbers in Excel, you can use the below formula.

To Find the Position

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

To Extract the Text

=LEFT(A1,position-1)

To Extract the Numbers

=RIGHT(A1,LEN(A1)-position+1)

Syntax Explanations:

MID – This function helps to return the smallest numeric value from the range of input values. Read more on the MID function.

FIND – In Excel, the FIND function is used to find out the one text string inside the other.

A1 – It represents the input value.

LEFT – It extracts the first character (starting from the left side) or characters from the given string based on the number you specified. Read more on the LEFT function.

RIGHT – The RIGHT function is used to extract the texts with the given number of characters from the right side.

LEN – This function helps to return the length of a given text string as the number of characters. Read more on the LEN function.

Comma symbol (,) – It is a separator that helps to separate a list of values.

Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Refer to the below example image.

Here we will enter the input values in Column B.

Now we are going to split the text strings into text and numbers.

Select any cell and type the above-given formula.

Finally, press the ** ENTER** key, you can get the result as shown below.

Verdict:

From this short tutorial, you can get some clarification on how to split the values into text and numbers in Excel. Hope you like this article. If you have any issues regarding this article or any unresolved queries , please comment in the comment box below. We will assist you.

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **

Read Ahead:

Excel Formulas to Split the Dimensions into Two Parts ~ Easily!!

How to Validate Only Numbers in Cell in Excel Office 365?

Excel Formulas to Replace One Character with Another ~ Quickly!!

How to Search for a Value in All Tabs in Excel Office 365?