Only count numeric values

[tmyers]

Out of pure curiosity, is there a way to only count numeric values within a cell that also contains text? Would save me a lot of editing if there was.
An example of a cell is this:
PRICE1 - 1500
PRICE2 - 2000
PRICE 3 - 1500

So I would say that cell has a total of 5000. I highly doubt it is possible, but never hurts to ask.

 

[Isaac]

I woiuld write a custom udf for it

 

[MajP]

Is it always that format? If so you can use string functions to get everything after "- ". If there are other formats you will have to show all possibilities.

Public Function GetAfterDash(Val As Variant) As Variant
  On Error GoTo errlbl
  If Not IsNull(Val) Then
    GetAfterDash = CLng(Trim(Split(Val, "-")(1)))
  End If
  Exit Function
errlbl:
  If Err.Number = 13 Then
    Debug.Print "bad format " & Val
  Else
    Debug.Print Err.Number & " " & Err.Description
  End If
End Function

 

[Isaac]

Is it always that format? If so you can use string functions to get everything after "- ". If there are other formats you will have to show all possibilities.

Public Function GetAfterDash(Val As Variant) As Variant
  On Error GoTo errlbl
  If Not IsNull(Val) Then
    GetAfterDash = CLng(Trim(Split(Val, "-")(1)))
  End If
  Exit Function
errlbl:
  If Err.Number = 13 Then
    Debug.Print "bad format " & Val
  Else
    Debug.Print Err.Number & " " & Err.Description
  End If
End Function

Maybe edit to loop through all 3(?) lines in the cell too

 

[MajP]

Did not see that this was Excel

Public Function GetSumAfterDash(Values As Range) As Double
  Dim i As Integer
  For i = 1 To Values.Rows.Count
     GetSumAfterDash = GetSumAfterDash + GetAfterDash(Values.Cells(i, 1))
  Next i
End Function

 

[tmyers]

It sadly isn't always in that format.

Small example.

 

[Isaac]

It sadly isn't always in that format.
View attachment 88002
Small example.

Can you upload a small section of that spreadsheet please?

 

[Isaac]

This seems to work OK for me:

Function GetNumericSums(rng As Range) As Double
Dim arrValues() As String, x As Long, dblTotal As Double, strValue As String
Dim strEntireValue As String
strEntireValue = Replace(Replace(rng.Value, Chr(13), " "), Chr(10), " ")
If InStr(1, strEntireValue, " ") > 0 Then
    'normal route: it has a space
    arrValues = Split(strEntireValue, " ")
    For x = 0 To UBound(arrValues)
        strValue = arrValues(x)
        'if the value of this chunk is numeric, (minus the $ sign and commas), then add it up:
        If (IsNumeric(Replace(Replace(strValue, ",", ""), "$", "")) = True) And Left(strValue, 1) = "$" Then
            dblTotal = dblTotal + CDbl(strValue)
        End If
    Next x
Else
    'abnormal route - it has no space
    If IsNumeric(Replace(Replace(strEntireValue, ",", ""), "$", "")) = True Then
        dblTotal = dblTotal + CDbl(strEntireValue)
    End If
End If

GetNumericSums = dblTotal

End Function

Note:

• It assumes that, within any "number chunk" (like $4,284.00), the only non-numeric things needing to be factored in, during the conversion to number, is a dollar sign and a comma

 

[Gasman]

It sadly isn't always in that format.
View attachment 88002
Small example.

Your first example had hyphens and all those have colons?

 

[tmyers]

Your first example had hyphens and all those have colons?

Depending on the column, it bounces back and forth. I am sadly not the one who enters all the data in. I am just the one who has to deal with it to handle reporting. I would say more often than not it is semi-colons however.

 

[tmyers]

Can you upload a small section of that spreadsheet please?

Here you go!

 

[Gasman]

Depending on the column, it bounces back and forth. I am sadly not the one who enters all the data in. I am just the one who has to deal with it to handle reporting. I would say more often than not it is semi-colons however.

Try Isaac's code.
If it was me, I'd try and standardise the split/find character, then locate and get anything to the right of that (also looking from right to left for the colon, and then use Val() on it.?

Only a pile of testing is going to show if that would be any good?

I once had a spreadsheet that I had to do something like that, replace characters and slowly the number of different characters grew and grew

 

[Isaac]

Here you go!

Ok, I posted code - it seems to work on a sample of about 20 cells that I tested in your file.

 

[MajP]

Depending on the column, it bounces back and forth. I am sadly not the one who enters all the data in. I am just the one who has to deal with it to handle reporting. I would say more often than not it is semi-colons however

Isaac's code would handle both, unless these guys decide to drop the spaces
-$23.00 or :$23.00
or if they have numerics that are not really a cost
2 DockingStations Model 12345

 

[tmyers]

I am going to sound super novice on this one.
It has been forever since I have done things in Excel other than formulas. How would you call this

 

[Isaac]

I am going to sound super novice on this one.
It has been forever since I have done things in Excel other than formulas. How would you call this

=GetNumericSums(A2)

 

[Isaac]

or if they have numerics that are not really a cost
2 DockingStations Model 12345

I'm checking for a dollar sign too..

 

[Isaac]

I'm checking for a dollar sign too..

Oops - I am not, b/c I forgot to post a small change to the code - try this one please

Function GetNumericSums(rng As Range) As Double
Dim arrValues() As String, x As Long, dblTotal As Double, strValue As String
Dim strEntireValue As String
strEntireValue = Replace(Replace(rng.Value, Chr(13), " "), Chr(10), " ")
If InStr(1, strEntireValue, " ") > 0 Then
    'normal route: it has a space
    arrValues = Split(strEntireValue, " ")
    For x = 0 To UBound(arrValues)
        strValue = arrValues(x)
        'if the value of this chunk is numeric, (minus the $ sign and commas), then add it up:
        If (IsNumeric(Replace(Replace(strValue, ",", ""), "$", "")) = True) And Left(strValue, 1) = "$" Then
            dblTotal = dblTotal + CDbl(strValue)
        End If
    Next x
Else
    'abnormal route - it has no space
    If IsNumeric(Replace(Replace(strEntireValue, ",", ""), "$", "")) = True Then
        dblTotal = dblTotal + CDbl(strEntireValue)
    End If
End If

GetNumericSums = dblTotal

End Function

 

[tmyers]

=GetNumericSums(A2)

As I say when I ask something dumb. Derp.
In my defense, I think I have used udf's in excel maybe twice and it was a long time ago lol.

 

[Isaac]

As I say when I ask something dumb. Derp.
In my defense, I think I have used udf's in excel maybe twice and it was a long time ago lol.

No worries, I almost never use them myself. They've always seemed a bit buggy and volatile (in the generic sense of the word), to me.
I'd rather give someone a button to push and a procedure that runs on a Selection, or something..But sometimes they are useful, if nothing else makes sense.

Good luck!