count only numbers

[legolas]

hello all,

suppose i have a cell of mixed text and numbers such as

thomas3650,bww 520,500240

what formula i need to count only numbers
in this case it is (3) what i am looking for

thanks

[DonkeyOte]

If every term has a number (and poss. text) and all values are delimited by a comma then count the commas and add 1

=1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

[legolas]

suppose i was able to remove text to become soomething like
a b c d
3650 320 500240

how do i get (3) in d

thanks

[DonkeyOte]

see COUNT function

[msd_2008]

Hi Donkeote,

I tried your Len formula and offcourse it worked. But I am unable to understand the logic behind the formula. Can you please explain for me in simple terms what does each part of that formula mean
=1+LEN(D26)-LEN(SUBSTITUTE(D26,",","")). I am at the beginners level in excel and trying to learn more. Thus, I want to be clear about the formula set up.

Thanks
MSD

[DonkeyOte]

MSD_2008, if you have your own question please ask in your own thread.

If you feel this thread is esp. relevant to your own question provide a link to it from your own.

Thanks,