Results 1 to 4 of 4

Thread: Adding Numbers with Text in Cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding Numbers with Text in Cell

    So I'm trying to add a row of cells that contain both letters and numbers. I'm using the following macros:

    Function add_num(cell1, ParamArray Arr() As Variant)

    Dim temp As Double
    For i = LBound(Arr) To UBound(Arr)
    temp = temp + GetNumber(Arr(i))
    Next

    add_num = GetNumber(cell1.Value) + temp
    End Function

    Function GetNumber(ByVal str As String) As Double
    Dim objRegEx As Object
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.IgnoreCase = True
    objRegEx.Global = True

    objRegEx.Pattern = "\d{1,2}([\.,][\d{1,2}])?"

    Set allMatches = objRegEx.Execute(str)

    For i = 0 To allMatches.Count - 1
    result = result & allMatches.Item(i)
    Next

    GetNumber = result
    End Function

    And in the cell I want to display the value I have:
    =add_num(H8,I8,J8,K8)

    That works fine until I have multiple numbers in 1 cell, such as N12ES1. Which it interprets as 121 instead of adding 12+1.

    Any ideas?

  2. #2
    MrExcel MVP Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,806
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Adding Numbers with Text in Cell

    Try this.
    Replace all your code with this:

    Code:
    Function GetNumbers(r As String)
    Dim m As Object, t
    With CreateObject("vbscript.regexp")
        .Pattern = "\d+"
        .Global = True
        If .test(r) Then
            Set m = .Execute(r)
            For Each t In m: GetNumbers = GetNumbers + Val(t): Next
        End If
    End With
    End Function
    
    Function AddNums(ParamArray Arr() As Variant)
    Dim t
    For Each t In Arr: AddNums = AddNums + GetNumbers(CStr(t)): Next
    End Function

    Excel 2010
    AB
    1N12ES142
    214
    3A15

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=addnums(A1,A2,A3)

    Last edited by Scott Huish; Oct 16th, 2019 at 06:49 PM.
    Office 2010/365

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,315
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Adding Numbers with Text in Cell

    Here is a way to do this without using Regular Expressions...
    Code:
    Function AddNums(ParamArray NumOrRng())
      Dim V As Variant, VV As Variant
      For Each V In NumOrRng
        If VarType(V) < vbArray Then
          AddNums = AddNums + GetNumSum(V)
        Else
          For Each VV In V
            AddNums = AddNums + GetNumSum(VV)
          Next
        End If
      Next
    End Function
    
    Function GetNumSum(ByVal S As String) As Long
      Dim X As Long
      For X = 1 To Len(S)
        If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
      Next
      GetNumSum = Evaluate(Replace(Trim(S), " ", "+"))
    End Function
    Note: The argument to the AddNums function (a comma delimited text string) can be one or more single numerical constants, one or more single range references, one or more multi-cell range references or any combination of those in any order.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    New Member
    Join Date
    Jan 2016
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Numbers with Text in Cell

    Thanks for your help, it worked perfectly.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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