Hi all, is it possible to sum cells that have both letters and figures
in?, i have lists of figures some of which look like this £3.5m, £5m
etc is it possible to write a formula which just sums the figures?

All help or suggestions appreciated.

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=571172

helper column with this idea
=SUBSTITUTE(SUBSTITUTE(E2,"L",""),"m","")
then try this array formula entered with ctrl+shift+enter
=SUM(VALUE(F2:F5))
--
Don Guillett
SalesAid Software

"Simon Lloyd"
wrote in message
...

Hi all, is it possible to sum cells that have both letters and figures
in?, i have lists of figures some of which look like this £3.5m, £5m
etc is it possible to write a formula which just sums the figures?

All help or suggestions appreciated.

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=571172

ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will strip out all of the alpha-characters......leaving just
the numerical ones to do your math with.

hth
Vaya con Dios,
Chuck, CABGx3


"Simon Lloyd"
wrote in message
...

Hi all, is it possible to sum cells that have both letters and figures
in?, i have lists of figures some of which look like this £3.5m, £5m
etc is it possible to write a formula which just sums the figures?

All help or suggestions appreciated.

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=571172

On Sun, 13 Aug 2006 12:43:32 -0400, Simon Lloyd
wrote:


Hi all, is it possible to sum cells that have both letters and figures
in?, i have lists of figures some of which look like this £3.5m, £5m
etc is it possible to write a formula which just sums the figures?

All help or suggestions appreciated.

Regards,
Simon

One way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then to extract a number, use this Regular Expression formula:

=REGEX.MID(A1,"(\d+(\.\d*)?)|(\.\d+)")

However, that extracts the number as a text string.

To change it into a number value, precede by a double unary:

=--REGEX.MID(A1,"(\d+(\.\d*)?)|(\.\d+)")

Unfortunately, that will result in an #VALUE! error if there is no number in
the string. If that is a problem, this formula will result in either the
value, or a zero.

=IF(REGEX.COMP(A1,"\d"),--REGEX.MID(A1,"(\d+(\.\d*)?)|(\.\d+)"),0)

If you'd rather have a null string that a zero:

=IF(REGEX.COMP(A1,"\d"),--REGEX.MID(A1,"(\d+(\.\d*)?)|(\.\d+)"),"")

Finally, if the values can be negative numbers, the formula can be modified,
but we need to know the format of a negative value.


--ron

Guys thanks for the replies!, Don i would like to use your suggestion as
an add-in is not an option because i would have to install the add-in on
every machine, could you give me a hand on how to use what you have
supplied......i did try adding it to a w/s but didnt actually get what
i needed, probably because the way i was using it

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=571172

Simon Lloyd Wrote:
Hi all, is it possible to sum cells that have both letters and figures
in?, i have lists of figures some of which look like this £3.5m, £5m
etc is it possible to write a formula which just sums the figures?

All help or suggestions appreciated.

Regards,
Simon
Hi Simon,

If your figures are in column A, then in B1

=SUBSTITUTE(SUBSTITUTE(A1,"£",""),"m","") then drag/copy this down,
then in the totals cell

=SUM(VALUE(B1:B3)) entered as an array - Ctrl:Shift:Enter so the the
braces appear like so

{=SUM(VALUE(B1:B3))}

Hope this helps

oldchippy :)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=571172

I thought I explained it but maybe Olechippy was more detailed?

--
Don Guillett
SalesAid Software

"Simon Lloyd"
wrote in message
...

Guys thanks for the replies!, Don i would like to use your suggestion as
an add-in is not an option because i would have to install the add-in on
every machine, could you give me a hand on how to use what you have
supplied......i did try adding it to a w/s but didnt actually get what
i needed, probably because the way i was using it

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=571172

On Sun, 13 Aug 2006 15:40:14 -0400, Simon Lloyd
wrote:


Guys thanks for the replies!, Don i would like to use your suggestion as
an add-in is not an option because i would have to install the add-in on
every machine, could you give me a hand on how to use what you have
supplied......i did try adding it to a w/s but didnt actually get what
i needed, probably because the way i was using it

Regards,
Simon

Simon,

Longre's morefunc.xll can be easily distributed with the workbook. No need to
have everyone install the add-in.

When you install the add-in on your machine, you can also (optionally) install
a morefunc sub-menu. One of the options is to "embed morefunc in this
workbook".


--ron

Again i'm overwhelmed by the replies, Don you did explain i managed it
after i sent the response, but as i get up at 5am it was a little late
to write back. Chippy thanks for the explanation it was clear and
concise. Ron i am on a network machine which has every file filtered
and checked before a recipient gets it, there is a maximum file size
and most machines have had their permissions for install or security
changes denied so i thought it may cause problems, but i thank you for
taking the time to explain.

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=571172