Add numbers accross columns after stripping away text
 

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

Hi Gavin,

One way

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

--

HTH

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


"gavin" wrote in message
...
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

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

"gavin" wrote in message
...
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

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


"N Harkawat" wrote in message
...
=SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
if its always "f"

"gavin" wrote in message
...
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,
On reflection can I just ask about HOW this function works? For example, I
have never seen a double hyphen before.


Regards,



Gavin



"Bob Phillips" wrote in message
...
Hi Gavin,

One way

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

--

HTH

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


"gavin" wrote in message
...
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

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



Regards,



Gavin



"N Harkawat" wrote in message
...
=SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
if its always "f"

"gavin" wrote in message
...
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

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)


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


Regards,



Gavin



"Bob Phillips" wrote in message
...
Hi Gavin,

One way

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

--

HTH

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


"gavin" wrote in message
...
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

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

"gavin" wrote:

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



Regards,



Gavin



"N Harkawat" wrote in message
...
=SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
if its always "f"

"gavin" wrote in message
...
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

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" wrote in message
...
Can I ask what the "0&" does? I have tried the formula without it and in
my
case it still works fine.



Regards,



Gavin



"N Harkawat" wrote in message
...
=SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
if its always "f"

"gavin" wrote in message
...
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

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



"Bob Phillips" wrote in message
...
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)


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


Regards,



Gavin



"Bob Phillips" wrote in message
...
Hi Gavin,

One way

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

--

HTH

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


"gavin" wrote in message
...
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

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





"Bob Phillips" wrote in message
...
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" wrote in message
...
Can I ask what the "0&" does? I have tried the formula without it and in
my
case it still works fine.



Regards,



Gavin



"N Harkawat" wrote in message
...
=SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
if its always "f"

"gavin" wrote in message
...
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