Add numbers accross columns after stripping away text

G

gavin

I have the following data in a spreadsheet:



A1 B1 C1 D1 E1 F1
4.5f 6f 3.5f 3f 7.25f


I need to be able to add the numbers together to give me 24.25, i.e. strip
the fs away. The numbers will always be less than 10 and the there will only
ever be .25 or.5 or .75 after the number (I don't know whether that is
significant?).


If anyone can show me how to do this I would be very grateful. I have been
messing around with MID and FIND to no avail and then started thinking that
SUMPRODUCT might have to get involved but it all got a bit much for me!
There may be a perfectly simple solution which I have overlooked!



Regards,




Gavin
 
B

Bob Phillips

Hi Gavin,

One way

=SUMPRODUCT(--(LEFT(A1:E1,LEN(A1:E1)-1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

gavin

Thanks so much to Bob and N Harkawat. Both solutions work great and the
speed of reply is unbelievable! I wouldn't have got there on my own so much
appreciated, guys!


Regards,



Gavin
 
G

gavin

Bob,
On reflection can I just ask about HOW this function works? For example, I
have never seen a double hyphen before.


Regards,



Gavin
 
G

gavin

Can I ask what the "0&" does? I have tried the formula without it and in my
case it still works fine.



Regards,



Gavin
 
B

Bob Phillips

Gavin,

It is added to cater for empty cells.

To show it clear one of the cells in your range, it will still work okay.

Now take the &0 out of the formula, and see what happens.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

gavin

Thanks for the link, Bob. I have seen SUMPRODUCT used many times (mostly in
answers to questions in this forum) in ways I wouldn't have had a clue
about. I think this web page will teach me a lot about that function!



Best wishes,



Gavin
 
G

gavin

Oh crikey - I've made a bit of a mistake! On reflection the range in the
formula could possibly contain a small number of other characters which it
needs to ignore. It could contain "s" or "l" - is there a way to make the
formula ignore these?


Thanks for more help!



Best wishes,



Gavin
 
Top