Add numbers accross columns after stripping away text

[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

 

[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)

 

[N Harkawat]

=SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
if its always "f"

 

[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

 

[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

 

[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

 

[Bob Phillips]

Gavin,

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html, it's all
explained there.

--

HTH

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

 

[Guest]

In my quick test, if there are empty cells I get an error *without* the "&0"

 

[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)

 

[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