## Explanation

In this example, the goal is to remove non-numeric characters from a text string with a formula.

Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW function with the INDIRECT function here:

```
ROW(INDIRECT("1:100"))
```

This snippet spins up an array containing 100 numbers like this:

```
{1,2,3,4,5,6,7,8...99,100}
```

*Note: 100 represents the maximum characters to process; adjust to suit your data.*

This array goes into the MID function as the **start_num** argument. For **num_chars**, we use 1. With these inputs, the MID function returns an array like this:

```
{"1";"0";"0";" ";"a";"p";"p";"l";"e";"s";"";"";"";""...}
```

*Note: extra items in the array removed for readability.*

To this array, we add zero. This is a simple trick that forces Excel to try and coerce a value to a number. Numeric text values like "1","2","3","4" etc. *are converted*, while non-numeric values *fail* and throw a #VALUE error. We use the IFERROR function to catch these errors and return an empty string (""), while numeric values pass through into an output array. The result is an array that contains just numbers and empty strings:

```
{1;0;0;"";"";"";"";"";....}
```

Finally, this array result is delivered to the TEXTJOIN function as the **text1** argument. For **delimiter**, we use an empty string ("") and for **ignore_empty** we supply TRUE, since we only want to work with the actual values that remain. TEXTJOIN then concatenates all non-empty values in the array and returns a final result.

*Note: TEXTJOIN will return the numbers as text, for example "100,"500", etc. If you want a true numeric result, you can add zero (+0), or wrap the entire formula in the VALUE function.*

### Strip numeric characters

To strip numeric characters, and preserve only text characters, you can use a formula like this:

```
{=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),""))}
```

This formula is explained here.