How to remove numbers from a cell value
This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula and it extracts everything except the numbers from cell B3 shown in the image above.
The following formula contains the TEXTJOIN function and it works only in Excel 2016.
It allows you to filter values up to 1000 characters and you can easily change that limit by changing this cell reference: $A$1:$A$1000 in the formula above.
Update 1/12/2021 new dynamic array formula in cell C3:
This formula works only with Excel 365, it contains the new SEQUENCE function that creates an array containing numbers from 1 to n.
Explaining the array formula in cell C3
The following part builds a cell reference that has as many values as there are characters in cell B3.
$A$1:INDEX($A$1:$A$1000, LEN(B3))
becomes
$A$1:INDEX($A$1:$A$1000, 7) and returns $A$1:$A$7.
The ROW function then converts the cell range to an array containing the numbers 1, 2, 3 ... up to the number of characters in cell B3.
ROW($A$1:INDEX($A$1:$A$1000, LEN(B3)))
becomes
ROW($A$1:$A$7) and returns this array: {1, 2, 3, 4, 5, 6, 7}.
The MID function then splits the values into an array of 7 values containing a character each.
MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)
becomes
MID(B3, {1, 2, 3, 4, 5, 6, 7}, 1) and returns {"A";"A";"1";"2";"3";"B";"B"}.
The TEXT function removes the numbers from the array.
TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), "")
becomes
TEXT({"A";"A";"1";"2";"3";"B";"B"}, "") and returns {"A";"A";"";"";"";"B";"B"}.
Lastly, the TEXTJOIN function concatenates all values in the array without a delimiting character, empty values are ignored.
TEXTJOIN("", TRUE, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), ""))
becomes
TEXTJOIN("", TRUE, {"A";"A";"";"";"";"B";"B"}) and returns AABB in cell C3.
Get Excel *.xlsx file
How to remove numbers from cell value.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
How to remove unwanted characters in a cell
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
How to replace part of formula in all cells
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
