Remove or exclude Text from cells, just leave numbers in Excel

[slagg7575]

Hi all!

I have a range of cells with numbers and text in them, IE, 50mg, 100mg
OID, 200md TID
How do I do the averages, counts, sums,of these numbers while exculding

the text part of the cell? I just want the numbers to be calculated.


Thanks in Advance,

Me

 

[flummi]

Hi Slagg7,

The easiest way would be a piece of VBA code.

But you can also do it with a 3 pass substitution:

1. Pass: substitute numbers 0 to 7 with ""
2. Pass. In the resulting string substitute numbers 8 and 9 with ""
3. Pass: In the original string substitute the result of pass 2 with ""

Here's the data:

10mg mg mg 10
80mg 8mg mg 80
100oil oil oil 100

Here's the formulae (replace the semicolons with commas if applicable):

1. pass
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"0";"");"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"")
2. pass
=SUBSTITUTE(SUBSTITUTE(B1;"8";"");"9";"")
3. pass
=VALUE(SUBSTITUTE(A1;C1;""))

Hope this helps

Hans