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