CODEWITHSUNDEEP
excel-2010
user2441751
user2441751

Reputation: 21

VALUE! Ignore text in excel cell calculation

I often run into VALUE! errors in my calculations because they contain numbers and text. How can I leave the text in the cell and proceed with the calculation?

For example:

cell A1 contents look like this: 101.1 J

cell A2 contents look like this: 500 U

cell A3 contents look like this: 0.2

If I want to add A1+A2+A3 into cell A4, how can I ignore the J and U to calculate 101.1+500+0.2 to get 602.3 in cell A4?

Thanks!

Upvotes: 2

Views: 69519

Answers (2)

Santosh
Santosh

Reputation: 12353

Using Custom Function

Place below code in Standard Module 

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

add_num function can be called from excel interface using =addnum(<cells>). It accepts multiple cells.

enter image description here

Upvotes: 3

Peter Albert
Peter Albert

Reputation: 17495

You need extract the values from the strings - and this can only be done if you have some kind of information about the format to the numbers. In your example, you could place the following formula in B1:B3 and then add a =SUM(B1:B3):

=IF(ISNUMBER(A1),A1,VALUE(LEFT(A1,SEARCH(" ",A1)-1)))

The above formula will extract the number and convert it to a value - unless it was already a number.

Upvotes: 4

Related Questions