Summing figures within letters???

  • Thread starter Thread starter Simon Lloyd
  • Start date Start date
S

Simon Lloyd

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
 
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
(e-mail address removed)
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
 
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 said:
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 :)
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top