*This tutorial will teach you how to separate text from numbers in Excel by using native formulas and custom functions. You will also learn how to split text and numbers into two separate columns.*

Imagine this: you receive raw data for analysis and find out that numbers are mixed with text in one column. In most situations, it will certainly be more convenient to have them in separate columns for closer examination.

In case you are working with homogeneous data, you could probably use the LEFT, RIGHT, and MID functions to extract the same number of characters from the same position. But that is an ideal scenario for laboratory tests. In real life, you are most likely to deal with dissimilar data where numbers come before text, after text or between text. The below examples provide solutions exactly for this case.

## How to remove text and keep numbers in Excel cells

*The solution works in Excel 365, Excel 2021, and Excel 2019*

Microsoft Excel 2019 introduced a few new functions that are not available in earlier versions, and we are going to use one of such functions, namely TEXTJOIN, to strip text characters from a cell containing numbers.

The generic formula is:

*cell*, ROW(INDIRECT( "1:"&LEN(

*cell*))), 1) *1, ""))

In Excel 365 and 2021, this one will also work:

*cell*, SEQUENCE(LEN(

*cell*)), 1) *1, ""))

At first sight, the formulas may look a bit intimidating, but they do work :)

For example, to remove text from numbers in A2, enter one of the below formulas in B2, and then copy it down to as many cells as needed.

In Excel 365 - 2019:

`=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2))), 1) *1, ""))`

In Excel 2019, it must be entered as an array formula with Ctrl + Shift + Enter. In dynamic array Excel, it works as a normal formula completed with the Enter key.

In Excel 365 and 2021:

`=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))`

As the result, all text characters are removed from a cell and numbers are kept:

**How this formula works:**

To better understand the logic, let's start investigating the formula from the inside:

You use either ROW(INDIRECT("1:"&LEN(string))) or SEQUENCE(LEN(string)) to create a sequence a numbers corresponding to the total number of characters in the source string, and then feed those sequential numbers to the MID function as the starting numbers. In B2, this part of the formula looks as follows:

`MID(A2, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}, 1)`

The MID function extracts each character from A2 beginning with the very first one and returns them as an array:

`{"2";"1";"0";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}`

This array is multiplied by 1. Numeric values survive with no change, while multiplying a non-numeric character results in a #VALUE! error:

`{2;1;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}`

The IFERROR function handles these errors and replaces them with empty strings:

`{2;1;0;"";"";"";"";"";"";"";"";"";"";"";""}`

This final array is served to the TEXTJOIN function, which concatenates the non-empty values in the array (*ignore_empty* argument set to TRUE) using an empty string ("") for the delimiter:

`TEXTJOIN("", TRUE, {2;1;0;"";"";"";"";"";"";"";"";"";"";"";""})`

Tip. For Excel 2016 - 2007, a solution also exists, but the formula is far more complex. You can find it in this tutorial: How to extract numbers in Excel.

### Custom function to remove text from numbers

*The solution works for all Excel versions*

If you are using an older version of Excel or find the above formulas too difficult to remember, nothing prevents you from creating your own function with a simpler syntax and a user-friendly name such as *RemoveText*. The user-defined function (UDF) can be written in two ways:

**VBA code 1:**

Here, we look at each character in the source string one by one and check if it's numeric or not. If a number, the character is added to the resulting string.

**VBA code 2:**

The code creates an object to process a regular expression. Using RegExp, we remove all characters other than digits 0-9 from the source string.

On small worksheets, both codes will perform equally well. On large worksheets where the function is called hundreds or thousands of times, code 2 that uses VBScript.RegExp will work faster.

The detailed steps to insert the code in your workbook can be found here: How to insert VBA code in Excel.

Whichever approach you choose, from the end-user perspective, the function to delete text and leave numbers is as simple as this:

For instance, to remove non-numeric characters from cell A2, the formula in B2 is:

`=RemoveText(A2)`

Just copy it down the column, and you'll get this result:

Note. Both the native formulas and custom function output a **numeric string**. To turn it into a number, multiply the result by 1, or add zero, or wrap the formula in the VALUE function. For example:

`=RemoveText(A2) + 0`

`=VALUE(RemoveText(A2))`

## How to remove numbers from text string in Excel

*The solution works in Excel 365, Excel 2021, and Excel 2019*

The formulas to remove numbers from an alphanumeric string are pretty much similar to the ones discussed in the previous example.

For Excel 365 - 2019:

*cell*, ROW(INDIRECT( "1:"&LEN(

*cell*) )), 1) *1), MID(

*cell*, ROW(INDIRECT("1:"&LEN(

*cell*))), 1), ""))

In Excel 2019, remember to make it an **array formula** by pressing the Ctrl + Shift + Enter keys together.

For Excel 365 and 2021:

*cell*, SEQUENCE(LEN(

*cell*1) *1), MID(

*cell*, SEQUENCE(LEN(

*cell*)), 1), ""))

For example, to strip numbers from a string in A2, the formula is:

`=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))`

Or

`=TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), ""))`

As the result, all numbers are removed from a cell and text characters are kept:

As shown in the screenshot above, the formula strips numeric characters from any position in a string: in the beginning, in the end, and in the middle. However, there is a caveat: if a string **starts with a number followed by a space**, that space is retained, which produces a problem of leading spaces (like in B2).

To get rid of extra **spaces before text**, wrap the formula in the TRIM function like this:

`=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "")))`

Now, your results are absolutely perfect!

**How this formula works:**

In essence, the formula works the same as explained in the previous example. The difference is that, from the final array served to the TEXTJOIN function, you need to remove numbers, not text. To have it done, we use the combination of IF and ISERROR functions.

As you remember, MID(…)+0 generates an array of numbers and #VALUE! errors representing text characters in the same positions:

`{2;1;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}`

The ISERROR function catches the errors and passes the resulting array of Boolean values to IF:

`{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}`

When the IF function sees TRUE (an error), it inserts the corresponding text character into the processed array with the help of another MID function. When the IF function sees FALSE (a number), it replaces it with an empty string:

`{"";"";"";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}`

This final array is passed over to TEXTJOIN, so it concatenates the text characters and outputs the result.

### Custom function to remove numbers from text

*The solution works for all Excel versions*

Keeping in mind that a robust formula should be kept simple, I will share the code of the user-defined function (UDF) to strip off any numeric character.

**VBA code 1:**

**VBA code 2:**

As is the case with the RemoveText function, the second code is better to be used in large worksheets to optimize the performance.

Once the code is added to your workbook, you can remove all numeric characters from a cell using this custom function:

In our case, the formula in B2 is:

`=RemoveNumbers(A2)`

To trim leading spaces if any, nest the custom function inside TRIM like you would a native formula:

`=TRIM(RemoveNumbers(A2))`

## Split numbers and text into separate columns

In situation when you want to separate text and numbers into two columns, it would be nice to have the job done with a single formula, agree? For this, we just merge the code of the *RemoveText* and *RemoveNumbers* functions into one function, named *SplitTextNumbers*, or simply *Split*, or whatever you like :)

**VBA code 1:**

**VBA code 2:**

Our new custom function requires two arguments:

Where *is_remove_text* is a Boolean value indicating which characters to strip:

- TRUE or 1 - remove text and keep numbers
- FALSE or 0 - remove numbers and keep text

For our sample dataset, the formulas take this form:

To remove non-numeric characters:

`=SplitTextNumbers(A2, TRUE)`

To delete numeric characters:

`=SplitTextNumbers(A2, FALSE)`

Tip. To avoid a potential problem of leading spaces, I recommend always wrapping the formula that removes numbers in the TRIM function:

`=TRIM(SplitTextNumbers(A2, FALSE))`

## Special tool to remove numbers or text

For those who do not like complicating things unnecessarily, I'll show our own way of removing text or numbers in Excel.

Assuming our Ultimate Suite is added to your Excel ribbon, this is what you do:

- On the
*Ablebits Data*tab, in the*Text*group, click**Remove**>**Remove Characters**.

- On the add-in's pane, select the source range, choose the
*Remove character sets*option, and pick either*Text**characters*or*Numeric**characters*in the drop-down list. - Hit
*Remove*and enjoy the result :)

Tip. If the results contain some leading spaces, the Trim Spaces tool will eliminate them in no time.

That's how to remove text or numeric characters from a string in Excel. I thank you for reading and look forward to seeing you on our blog next week!

## Available downloads

Remove text or numbers in Excel - examples (.xlsm file)

Ultimate Suite - trial version (.exe file)

## 52 comments

How do I delete some of these numbers

Original data: 00004567891234

Result : 04567891234

Hi! Use a mathematical operation to convert a number written as text to a normal number and add the text string "0". Read more: Turn text into number with mathematic operations. For example:

="0"&(A1+0)

Hi,

How to only get 31 in specific cell, from 31 mm written in other cell, a formula to be dragged down.

Have you tried the methods described in this blog post? If you are not satisfied, please let me know and I will try to help you.

I have a numbered list in text form which I want to convert to excel. When I copied it, it also copied the number. How can I get rid of the numbers before the names? Thank you.

1. Ellie

2. Atkins

Hi! Determine the position of the first space using the SEARCH function. Replace this number of characters with a blank using the REPLACE function.

=REPLACE(A2,1,SEARCH(" ",A2),"")

Or you can use the MID function to extract a string of text from the text, starting from the position of the first space.

=MID(A2,SEARCH(" ",A2)+1,50)

I have this value as text in on cell :

70 198,59 €

How can I transfer it into a number? I need to keep the decimal, (also there is a space in between the thousand).

Hi! To extract a number from text while leaving the decimal separator, you can try this formula:

=--TEXTJOIN("", TRUE, IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1, IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=",",",","")))

You can also find useful information in this article: How to extract number from string in Excel.

Set the formula cell to the number format that you want to use.

Hello team, can you help me with this?

I want to only extract the names for this:

4501: (1)Ali Tfayli

I only need Ali Tfayli, is there a formula I can use? I tried:

=TEXTJOIN("", TRUE, IF(ISERROR(MID(O2, SEQUENCE(LEN(O2)), 1) *1), MID(O2, SEQUENCE(LEN(O2)), 1), ""))

But the result is:

: ()Ali Tfayli

Hi! Look for the example formulas here: Extract text after a certain character. For example:

=RIGHT(A1,LEN(A1)-SEARCH(")",A1))

To extract text after a certain character, you can also use the new TEXTAFTER function:

=TEXTAFTER(A1,")")

Hi!

I am trying to extract only the department names, for example:

170M - Development needs to be Development

171M - Development WC needs to be Development WC

I tried using this: TEXTJOIN("",TRUE,IF(ISERR(MID(text,ROW(INDIRECT("1:"&LEN(text))),1)+0),MID(text,ROW(INDIRECT("1:"&LEN(text))),1),""))

However, I am left with dash marks and spaces..

Is there a formula that can be created and used as a drag down formula?

Thanks!

Hi! If I understand your task correctly, the following formula should work for you:

=TRIM(MID(A1,SEARCH("-",A1)+1,100))

For more information, please visit: How to split text string in Excel by comma, space, character or mask. The TRIM function will remove the extra spaces.

OMG Thank you sooooooooo much! This just saved me so much time!

Super helpful! TY!

I need the cell to only populate the first 40 characters. I need to trim off anything longer than 40 characters.

Hi! If I understand your task correctly, use LEN function to get first 40 characters from text string.

Can I know how to Split ( R SAMUEL 22SPUCH23) single cell data to split after (R SAMUEL) ( 22SPUCH23). How, It is possible in Excel

Hi! To split text by a second space, you can use the new TEXTBEFORE and TEXTAFTER functions.

=TEXTBEFORE(A1," ",2)

=TEXTAFTER(A1," ",2)

You can also find useful information in this article: How to split text string in Excel by comma, space, character or mask.

Try these formulas:

=LEFT(SUBSTITUTE(A1," ","#",2),SEARCH("#",SUBSTITUTE(A1," ","#",2))-1)

=MID(SUBSTITUTE(A1," ","#",2),SEARCH("#",SUBSTITUTE(A1," ","#",2))+1,20)

Hello, can you help me on this:

23-02-0201238549 to 201238549

23-02-1216228450 to 216228450

20-27-1116187482 to 116187482

23-03-0510238759 to 510238759

I need to remove the first 5 digit nos. Can you help me?

thanks

Hi! If I understand your task correctly, to remove symbols from text string try to use REPLACE function:

=REPLACE(A1,1,7,"")

Hi,

how to removeonly one space in cell?

example

B GCC 43 --> B GCC43

Hi! To remove the second space in a text string, use the SUBSTITUTE function.

Please use the formula below:

=SUBSTITUTE(A2," ","",2)

how to remove space from 0P 4832

Hi! To remove space from text string, use SUBSTITUTE function or other ways as described in this article: How to remove special (unwanted) characters from string in Excel.

Hello! I am trying to use the =TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2))), 1) *1, "")) function to extract a the number from a column such as:

0.5 ML

1 EA

0.2 ML

1 ML

This works great when the number is not a decimal but with the 0.5 for example it translates into 05. Anyway to use this function while preserving the decimal?

Hi! To extract a number along with the decimal separator from the text, try this formula:

=TEXTJOIN("",TRUE, IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1, IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=".",".","")))

Sir/madam,

When analyzing regression I get F value, significant value and p value as #NUM!

Kindly tell how to clear this

Thanking you

Regards

C Jayasundari

ACS98332

CB10311A

CBS10351A

how can I remove the text from the above codes

Hi!

Please re-read the article above, it covers your case completely. You can also find useful information in this article: Extract number from text string.

QAR 800.00

hi, anyone can you help me how to remove this "QAR" in the currency.

Hello!

Use the formula to extract the last word from the text.

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

0:59972400

1:67140000

2:24948000

3:19728000

Can I know how to remove the "0: & 1: & 2:" from the above numbers

Hello!

To extract part of the text starting from the third character, use the MID function:

MID(A1,3,100)

0:599724001324

1:67140000

2:2494

3:19728000234

if we have a random number how?

Can I know how to remove the "0: & 1: & 2:" from the above numbers

Hi!

To replace multiple groups of characters in text, use the SUBSTITUTE function. Replacing with a blank means deleting a character.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"2:",""),"1:",""),"0:","")

Thank you Alex

In B1:

=TEXTJOIN("",TRUE,SUBSTITUTE(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9}&":",""),A1,""))

That was very helpful, thank you.

Dear Alexander Trifuntov cordial greetings

I am working with a series of codes as follows

100001-PTR012

100001-PV012

I need to extract the non-numeric characters located after the hyphen (-), for which I have used the following formula:

=MID(A1,FIND("-",A1)+1,FIND("0",A1,FIND("-",A1))-FIND("-",A1)-1)

So far everything works

The problem arises when in the code, after the hyphen, the numeric characters begin with a non-zero (0) character, for example:

100001-PTR912

100001-PV512

100001-PVEW71204

What would be the best way to obtain only the non-numeric characters located after the hyphen, when they are preceded by non-zero numbers?

I appreciate your valuable collaboration.

Hello!

If I got you right, the formula below will help you with your task:

=LEFT(MID(A1,SEARCH("-",A1)+1,50), MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0}, MID(A1,SEARCH("-",A1)+1,50),1),10000))-1)

Use the LEFT function to start your search at the character following the "-".

Dear thank you very much for your help. It works correctly, a big hug!

Hi, I'm using the RemoveText custom function but it doesn't preserve decimals. For example, 950.43 becomes 95043. Is there a way to save the decimals?

Hello!

The comma is considered a character, not a number. Therefore, it must be added to the list of symbols that are not removed.

VBA code 2:

.Pattern = "[^0-9\,]"

You can also use regular expressions to remove specific characters from text. Here is a detailed instruction: Excel Regex to remove certain characters or text from strings.

In the RegExpReplace function, use the pattern "[^0-9\,]"

Thanks! That worked perfectly!

Hello,

It's the perfect solution. How to make an empty cell for the blank cell? Since we drag the formula down, the empty cell grabs some wired string and special character.

Thank you!

Hi Evan,

Nest your formula in the IF function like this:

=IF(A2="", "", your_formula)

Where A2 is the cell with the original data.

Hi Svetlana,

This is such a cool example; thank you for providing it. In my data I am trying to pull out numeric value from my text which is listed for example as 70 MPH. There could be other numeric values after the MPH but I dont want those. Can this formula be adjuster to only pull the numberic value before MPH? Thanks!

Hello!

Try to substitute this expression in the formula instead of A2:

LEFT(A2,SEARCH("MPH",A2)-1)

I hope it’ll be helpful.

Hi Jodi,

If you are looking for a universal solution that can pull number before any text, please see How to extract number from the beginning of text string.

Hi Svetlana,

what is the software you use to highlight your images in all your articles?

Kind regards

Hi Sukumar,

It's FastStone Capture.

Thank you

Regards