|
Summing figures within letters???
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 |
Summing figures within letters???
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 |
Summing figures within letters???
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 |
Summing figures within letters???
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 |
Summing figures within letters???
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 |
Summing figures within letters???
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 |
Summing figures within letters???
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 |
Summing figures within letters???
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 |
| All times are GMT +1. The time now is 11:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com