# Thread: Summing AlphaNumeric (Excel 2003)

1. ## Summing AlphaNumeric (Excel 2003)

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

2. ## Re: Summing AlphaNumeric (Excel 2003)

<P ID="edit" class=small>(Edited by mbarron on 18-Dec-08 14:39. added addendum )</P>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

3. ## Re: Summing AlphaNumeric (Excel 2003)

Hi Mike

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

regards, francis

4. ## Re: Summing AlphaNumeric (Excel 2003)

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.

5. ## Re: Summing AlphaNumeric (Excel 2003)

Hi Hans

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

TIA

regards, francis

6. ## Re: Summing AlphaNumeric (Excel 2003)

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)

7. ## Re: Summing AlphaNumeric (Excel 2003)

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

8. ## Re: Summing AlphaNumeric (Excel 2003)

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...

9. ## Re: Summing AlphaNumeric (Excel 2003)

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(LEF T(E2:E6,2)*1),LEFT(E2:E6,1)*1,LEFT(E2:E6,2)*1)))}

10. ## Re: Summing AlphaNumeric (Excel 2003)

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

11. ## Re: Summing AlphaNumeric (Excel 2003)

=SUM( <font color=red>IF(ISERROR(LEFT(E2:E6,1)*1),0</font color=red> , <font color=blue>IF(ISERROR(LEFT(E2:E6,2)*1),LEFT(E2:E6, 1)*1</font color=blue> , <font color=448800>LEFT(E2:E6,2)*1</font color=448800> )))

<font color=red>checks to see if the first character is a number. If it is not a number the value assigned is 0</font color=red>
<font color=blue>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 </font color=blue>
<font color=448800>returns the value for the first two characters</font color=448800>

12. ## Re: Summing AlphaNumeric (Excel 2003)

Thanks, Mike

This is greatly appreciates

cheers, francis

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•