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" <no-one@home.com> wrote in message
news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> 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" <no-one@home.com> wrote in message
news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
>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" <nh@nh.com> wrote in message
news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> if its always "f"
>
> "gavin" <no-one@home.com> wrote in message
> news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> >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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ON5MP$VXFHA.1152@tk2msftngp13.phx.gbl...
> Hi Gavin,
>
> One way
>
> =SUMPRODUCT(--(LEFT(A1:E1,LEN(A1:E1)-1)))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "gavin" <no-one@home.com> wrote in message
> news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > 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" <nh@nh.com> wrote in message
news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> if its always "f"
>
> "gavin" <no-one@home.com> wrote in message
> news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> >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" <no-one@home.com> wrote in message
news:d6ldsf$5ju$1@news6.svr.pol.co.uk...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:ON5MP$VXFHA.1152@tk2msftngp13.phx.gbl...
> > Hi Gavin,
> >
> > One way
> >
> > =SUMPRODUCT(--(LEFT(A1:E1,LEN(A1:E1)-1)))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "gavin" <no-one@home.com> wrote in message
> > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > > 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" <nh@nh.com> wrote in message
> news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> > =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> > if its always "f"
> >
> > "gavin" <no-one@home.com> wrote in message
> > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > >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" <no-one@home.com> wrote in message
news:d6le8p$m3b$1@newsg2.svr.pol.co.uk...
> 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" <nh@nh.com> wrote in message
> news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> > =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> > if its always "f"
> >
> > "gavin" <no-one@home.com> wrote in message
> > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > >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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uS4u7pXXFHA.3732@TK2MSFTNGP10.phx.gbl...
> 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" <no-one@home.com> wrote in message
> news:d6ldsf$5ju$1@news6.svr.pol.co.uk...
> > 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:ON5MP$VXFHA.1152@tk2msftngp13.phx.gbl...
> > > Hi Gavin,
> > >
> > > One way
> > >
> > > =SUMPRODUCT(--(LEFT(A1:E1,LEN(A1:E1)-1)))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "gavin" <no-one@home.com> wrote in message
> > > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > > > 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OdqeqrXXFHA.3584@TK2MSFTNGP14.phx.gbl...
> 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" <no-one@home.com> wrote in message
> news:d6le8p$m3b$1@newsg2.svr.pol.co.uk...
> > 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" <nh@nh.com> wrote in message
> > news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> > > =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> > > if its always "f"
> > >
> > > "gavin" <no-one@home.com> wrote in message
> > > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > > >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
> > > >
> > > >
> > >
> > >
> >
> >
>
>