Summing AlphaNumeric (Excel 2003)

Topic

New Reply

Hi

I have the following in a column, and I need to sum the number,

10kk
15mi
5mgi
n/a
20kk
30mgi

TIA

regards, francis

Reply | Quote

Replies

(Edited by mbarron on 18-Dec-08 14:39. added addendum )

A non UDF formula to extract the numbers from the beginning of the text is:
=MAX(IF(ISERROR(VALUE(MID(A2,1,ROW($A$1:$A$10)))),0,VALUE(MID(A2,1,ROW($A$1:$A$10)))))

Change the 10 in the “$A$1:$A$10″s to the largest digit count you may encounter.

addendum: I forgot to mention that this is an array formula and needs to be confirmed with Ctrl+Shift+Enter

Reply | Quote

Hi Mike

Thanks but this don’t sum up the numbers, its merely return the first digit in the string

regards, francis

Reply | Quote

You can use Mike Barron’s formula to extract the number part of each value, for example in the column next to the text values.
Then use a standard SUM formula to add these numbers.
See the attached workbook.

Reply | Quote

Hi Hans

Thanks. Is there no one formula that can do this…ie extract the number and sum it

TIA

regards, francis

Reply | Quote

If you’re willing to use a user-defined VBA function

Function SumNumbers(rng As Range)
Dim oCell As Range
For Each oCell In rng
SumNumbers = SumNumbers + Val(oCell)
Next oCell
End Function

You can use a formula like this:

=SumNumbers(A2:A7)

Reply | Quote

Hans

thank for the effort in creating this UDF. I am looking for a formula.
I have this array formula that I have created after hours but it only produce where characters are “mi”. how can I adjust it to include variable characters?

=SUM(IF(RIGHT(A1:A7,2)=”mi”,–LEFT(A1:A7,LEN(A1:A7)-2),0))

TIA

regards, francis

Reply | Quote

But what if a cell contains “97cmi”? You’d get an error because there is a letter to the left of “mi”.

It would be a lot easier if you entered the numbers in one column and the text strings in another column…

Reply | Quote

The following array formula will add up the numbers providing there are a maximum of 2 numbers in the the beginning of the string.

{=SUM(IF(ISERROR(LEFT(E2:E6,1)*1),0,IF(ISERROR(LEFT(E2:E6,2)*1),LEFT(E2:E6,1)*1,LEFT(E2:E6,2)*1)))}

Reply | Quote

Mike

this is excellent! thanks.I have spend hours trying to to what you did in minutes
Would you explain the formula you provide if this is not too much to ask?

cheers, francis

Reply | Quote

=SUM( IF(ISERROR(LEFT(E2:E6,1)*1),0 , IF(ISERROR(LEFT(E2:E6,2)*1),LEFT(E2:E6,1)*1 , LEFT(E2:E6,2)*1 )))

checks to see if the first character is a number. If it is not a number the value assigned is 0
since the first character is a number, this portion checks to see if the first two characters are a number. If the second character is not a number, the value of the first is returned
returns the value for the first two characters

Reply | Quote

Thanks, Mike

This is greatly appreciates

cheers, francis

Reply | Quote