Excel: extract and sum numerals from mixed text/numeral cell range

[bssmith]

I have a large (30x20) grid of cells with data, and I want to extract
and then sum up certain numerals from this entire range. The catch is
that the data is mixed numerals and text, as you'll see below.

Here's an abbreviated 3x3 example, with a value in each of the nine
cells:

V7.1 T H
P1 A T
B V3 P4.5

If I just wanted to sum up the instances of "T" appearing, I could use
COUNTIF() for the entire range to come up with answer ("T" appears 2
times). Easy enough.

But, what I'm trying to accomplish is to sum up the numerals associated
with other letters. Specifically, I'd like to sum up the numbers
associated with the letter "V"; there are two entries above, "V7.1" and
"V3", so I'd like to chop off the letters and sum the numerals. The
answer would be 10.1 (the sum of 7.1 and 3).

But how would I do this programmatically?

-- B.

 

[Ron Rosenfeld]

I have a large (30x20) grid of cells with data, and I want to extract
and then sum up certain numerals from this entire range. The catch is
that the data is mixed numerals and text, as you'll see below.

Here's an abbreviated 3x3 example, with a value in each of the nine
cells:

V7.1 T H
P1 A T
B V3 P4.5

If I just wanted to sum up the instances of "T" appearing, I could use
COUNTIF() for the entire range to come up with answer ("T" appears 2
times). Easy enough.

But, what I'm trying to accomplish is to sum up the numerals associated
with other letters. Specifically, I'd like to sum up the numbers
associated with the letter "V"; there are two entries above, "V7.1" and
"V3", so I'd like to chop off the letters and sum the numerals. The
answer would be 10.1 (the sum of 7.1 and 3).

But how would I do this programmatically?

-- B.

Well, here's a simple UDF that will do it, provided your data is in the format
you show above.

To enter the UDF, <alt><F11> opens the Visual Basic Editor. Ensure your
project is highlighted in the Project Explorer, then Insert/Module and paste
the code below into the window that opens.

To use this, enter the function =SumDigByLtr(rg,ltr) where rg is the range you
wish to check, and ltr is the letter you want to get the sum for. The letter
is case sensitive and must be enclosed in quotes.

e.g : =sumdigbyltr(E4:G6,"P")

===================
Option Explicit
Function SumDigByLtr(rg As Range, ltr As String) As Double
Dim c As Range

For Each c In rg
If InStr(1, c.Text, ltr) > 0 Then
SumDigByLtr = SumDigByLtr + Replace(c.Text, ltr, "")
End If
Next c
End Function
=====================

--ron

 

[Nick Hodge]

B

Something like the following will do it. (Copy it to a standard module in
the workbook and then use it like a normal function), e.g if the data is in
A1:C3 and you are looking for cells beginning "P"

=sumvalues(A1:C3,"P")

In your example gives an answer of 5.5


Function SumValues(rArea As Range, sLetter As String) As Double
Dim myCell As Range
Dim dTempVal As Double
Application.Volatile True
For Each myCell In rArea
If Left(myCell.Value, 1) = sLetter Then
dTempVal = dTempVal + Mid(myCell.Value, 2, Len(myCell.Value) - 1)
End If
Next myCell
SumValues = dTempVal
End Function


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Dave Peterson]

You could do it with worksheet functions, too:

=SUM(IF(ISNUMBER(--SUBSTITUTE(UPPER(A1:C1925),"V","")),
--SUBSTITUTE(UPPER(A1:C1925),"V","")))

(all one cell, adjust the range accordingly)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)