Results 1 to 12 of 12

20081218, 04:58 #1
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20081218, 06:39 #2
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Summing AlphaNumeric (Excel 2003)
<P ID="edit" class=small>(Edited by mbarron on 18Dec08 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

20081218, 22:37 #3
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20081218, 23:07 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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.

20081218, 23:13 #5
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20081218, 23:24 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Summing AlphaNumeric (Excel 2003)
If you're willing to use a userdefined 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)

20081219, 02:07 #7
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20081219, 02:45 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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...

20081219, 03:01 #9
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)))}

20081219, 03:28 #10
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20081219, 03:33 #11
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>

20081219, 14:38 #12
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Summing AlphaNumeric (Excel 2003)
Thanks, Mike
This is greatly appreciates
cheers, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array