This rather brilliant formula will do the job for you.

If your mix of numbers and text is in cell A1, enter this in the next cell, pressing `Ctrl`+`Shift`+`Enter`:

```
=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))
```

You can then sum this column to calculate your total.

This formula was originally posted to Google Groups by somebody called Lori - I read about it on a Chandoo forum thread from a post by Sajan. How it works:

The magic of NPV is the NPV calculation formula, where each term is
multiplied by the inverse of (1+rate)^n, where n is the nth term in
the series. e.g. (1+rate)^1, (1+rate)^2, etc. By using different
values for rate, we can get different results. In this case, using
-0.9 gives us 1+rate=1+-0.9=0.1. So we get values like {0.1;0.01;0.001;0.0001;0.00001}. Taking the inverse of this gives us
{10;100;1000;10000;100000} etc. Combined with the fact that NPV skips
text values, we get the desired results.

**Edit: Improved Calculation Speed**

Máté Juhász suggested an addition to speed up the formula if applied to a large range. It checks if the string is already a number first, to save Excel time calculating something it doesn't need to.

```
=IF(ISNUMBER(A1),A1,NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,"")))
```