Retrieve only numbers in cell

[Dina01]

Hello,

I have an excel sheet that I need to calculate only the numbers in the cell, but my cells contains numbers and letters. How can I do this. The worksheet looks like this.....
_________________________
dina 23| cathy 12 | lal 01
_________________________

Therefore I would need to total the amount but I need to retrieve only the numbers to be able to calculate.

Can anyone please tell me how to do this...

Thanks

 

[xlbo]

If the numbers are always preceded by a space and there isonly 1 space per cell then this will work:

=SUMPRODUCT((VALUE(RIGHT(A1:C1,LEN(A1:C1)-FIND(" ",A1:C1)))))

where your data is in A1:C1

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Dina01]

Thanks you I will try that right away...

 

[grrr72]

Otherwise you could have a look at parse_data spreadsheet on

http://www.xl-logic.com/pages/vba.html

which gives you the option to parse more complicated cell contents. It could be adapted to your requirements quite simply. It basically apes the 'Text to Columns' function.

Regards,
Nic