Author: Oscar Cronquist Article last 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.

=TEXTJOIN("", TRUE, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), ""))

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

How to remove numbers from cell value.xlsx