*This tutorial will teach you how to separate text from numbers in Excel by using native formulas and custom functions.*

Imagine this: you receive raw data for analysis and find out that numbers are mixed with text in one column. In most cases, 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.

*The solution works in Excel 365 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:

TEXTJOIN("", TRUE, IFERROR(MID(*cell*, ROW(INDIRECT( "1:"&LEN(*cell*))), 1) *1, ""))

In Excel 365, this one will also work:

TEXTJOIN("", TRUE, IFERROR(MID(*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 drag it down through as many cells as needed.

In Excel 365 and 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 Excel 365, it works as a normal formula completed with the Enter key.

In Excel 365:

`=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;"";"";"";"";"";"";"";"";"";"";"";""})`

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

Function RemoveText(str As String) Dim sRes As String sRes = "" For i = 1 To Len(str) If True = IsNumeric(Mid(str, i, 1)) Then sRes = sRes & Mid(str, i, 1) End If Next i RemoveText = sRes End Function

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

Function RemoveText(str As String) As String With CreateObject("VBScript.RegExp") .Global = True .Pattern = "[^0-9]" RemoveText = .Replace(str, "") End With End Function

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:

RemoveText(string)

For instance, to remove text characters from a string in A2, the formula in B2 is:

`=RemoveText(A2)`

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

`=RemoveText(A2) + 0`

`=VALUE(RemoveText(A2))`

*The solution works in Excel 365 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 2019 and 365:

TEXTJOIN("", TRUE, IF(ISERR(MID(*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:

TEXTJOIN("", TRUE, IF(ISERROR(MID(*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.

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

Function RemoveNumbers(str As String) Dim sRes As String sRes = "" For i = 1 To Len(str) If False = IsNumeric(Mid(str, i, 1)) Then sRes = sRes & Mid(str, i, 1) End If Next i RemoveNumbers = sRes End Function

**VBA code 2:**

Function RemoveNumbers(str As String) As String With CreateObject("VBScript.RegExp") .Global = True .Pattern = "[0-9]" RemoveNumbers2 = .Replace(str, "") End With End Function

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:

RemoveNumbers(string)

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))`

In situation when text and numbers need to be separated into different 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:**

Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String Dim sNum, sText, sChar As String sCurChar = sNum = sText = "" For i = 1 To Len(str) sCurChar = Mid(str, i, 1) If True = IsNumeric(sCurChar) Then sNum = sNum & sCurChar Else sText = sText & sCurChar End If Next i If True = is_remove_text Then SplitTextNumbers = sNum Else SplitTextNumbers = sText End If End Function

**VBA code 2:**

Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String With CreateObject("VBScript.RegExp") .Global = True If True = is_remove_text Then .Pattern = "[^0-9]" Else .Pattern = "[0-9]" End If SplitTextNumbers = .Replace(str, "") End With End Function

Our new custom function requires two arguments:

SplitTextNumbers(string, is_remove_text)

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

`=SplitTextNumbers(A2, TRUE)`

To remove numbers:

`=SplitTextNumbers(A2, FALSE)`

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

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

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!

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

Ultimate Suite - trial version (.zip file)

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## One response to "How to remove text from Excel cell and leave numbers or vice versa"

Hi Svetlana,

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

Kind regards