Summing numeric parts of alphanumeric values

**amdk8800** · 11-10-2009, 08:06 PM

Hi,

I am wondering if you can sum the numeric parts of a range of cells containing alphanumeric values. Here is an example:

values to sum: 500, a20, ab30
expected results: 500 + 20 + 30 = 550.

In this project, the number of values to sum is large and I do not wish to use long formulas to extract the numeric part of each cell within the range. I am also forbidden to use up extra cells as an intermediate working out. Can you help?

Cheers guys.

**JBeaucaire** · 11-10-2009, 08:23 PM

Forbidden? By whom?

I would use VBA to construct a User Defined Function especially to accomplish this. Is VBA forbidden, too?

**daddylonglegs** · 11-10-2009, 08:23 PM

Is the numeric part always at the end as per your example?

How many letters might there be, 1 2 .....or more?

Are there many different letters or only a few?

**amdk8800** · 11-10-2009, 08:48 PM

Hi JBeaucaire,

I said forbidden because my workbook is referenced to by other files and I don't want to screw up the existing format. I have considered using VBA too but I am not proficient enough to make it update the sum whenever the data in the range changes. Plus I want this to happen to several worksheets within this workbook, the scripting can be quite nasty.

**amdk8800** · 11-10-2009, 08:51 PM

Hi Daddylonglegs,

I am happy if you can solve this for the case where the values within the range is either numeric or take the form "a"+number (eg. 30, a50, a80).

**daddylonglegs** · 11-10-2009, 09:02 PM

If the letter is always "A" then you could use this formula

=SUMPRODUCT(SUBSTITUTE(0&UPPER(A1:A100),"A","")+0)

or for any letters but only either a single one at the start or none at all

=SUMPRODUCT((0&MID(A1:A100,1+ISERR(LEFT(A1:A100)+0),10))+0)

Where data is in the range A1:A100

both formulas will also allow blanks in the range

**amdk8800** · 11-10-2009, 09:22 PM

Hi Daddylonglegs,

Thanks for your prompt assistance. Your formula works well. But I am stumped when trying to incorporate this with a SUMIF function. I mean my range is a row under different column headings. I wish to sum those cells with column heading begining with "Actual". Normally without the alphanumeric issue, I would use something like:

=SUMIF($A$1:$AB$1, "Actual*",A2:AB2)

Any suggestion?

**daddylonglegs** · 11-10-2009, 09:31 PM

You can do that within SUMPRODUCT, so for the first formula I posted try

=SUMPRODUCT(SUBSTITUTE(0&UPPER(A2:AB2),"A","")*(LEFT(A1:AB1,6)="Actual"))

**amdk8800** · 11-10-2009, 09:57 PM

Hi Daddylonglegs, This works perfectly. You are a legend. Thanks for the trick.

**acls2x** · 11-17-2009, 07:04 PM

I can use the below if my cells are either blank or contain a text string with an "a". Is there a way to adjust this so that the numeric portions of only the cells with an a in them get summed.

If I use the below and my range contains any letter other than a I get an error i would like cells with other letters to be somehow ignored

IE my column may contain 8a, 4a and the summing works great but if my column contains 8a, 4a, m I get an error

Originally Posted by daddylonglegs

If the letter is always "A" then you could use this formula

=SUMPRODUCT(SUBSTITUTE(0&UPPER(A1:A100),"A","")+0)

or for any letters but only either a single one at the start or none at all

=SUMPRODUCT((0&MID(A1:A100,1+ISERR(LEFT(A1:A100)+0),10))+0)

Where data is in the range A1:A100

both formulas will also allow blanks in the range

**daddylonglegs** · 11-17-2009, 08:27 PM

Hello aclx2s,

Please can you start a new thread with your question. If you think this thread is relevant then please quote the formulas and/or link back to this thread, thanks