Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    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, francis
    Hope 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

  2. #2
    5 Star Lounger
    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 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. #3
    3 Star Lounger
    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, francis
    Hope 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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    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, francis
    Hope 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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    3 Star Lounger
    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, francis
    Hope 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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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...

  9. #9
    5 Star Lounger
    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)))}

  10. #10
    3 Star Lounger
    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, francis
    Hope 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

  11. #11
    5 Star Lounger
    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>

  12. #12
    3 Star Lounger
    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, francis
    Hope 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

Posting Permissions

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