## How to remove numbers from a cell value

*Article updated on March 25, 2018*

The array formula in cell C3:C7 extracts everything except numbers from cell B3.

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.

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

### Download Excel *.xlsx file

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article