Add number before text that matches criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carole - 06 May 2008 21:45 GMT

A1 = 1 A
B1 = 1.5 S
3 A
A2 = 4.25 S
B2 = 8 S

I have the following formula to look for text within a range of cells and
add the number before the text.
=SUM(IF(ISNUMBER(SEARCH("A",$A1:$A2)),--SUBSTITUTE($A1:$A2,"
"&IF(RIGHT($A1:$A2)="A","A",""),"")))

This works great when there is only one instance of text within the cell.
When I have more than one instance of text seperated by ALT Tab, I get ###
error. I need to find the text that meets the criteria (A or S) and add the
number before that text.

What do I need to add or change in my formula to look for the "A" in B1 and
add 3 to my total?

Reply to this Message

ryguy7272 - 08 May 2008 20:30 GMT

Here is one option:
Function ExtractNumber(rCell As Range) As Double
Dim X As Long
For X = 1 To Len(rCell.Value)
If Mid$(rCell.Value, X, 1) Like "*[0-9.]" Then
ExtractNumber = Val(Mid$(rCell.Value, X))
Exit For
End If
Next
End Function

Extract the numbers and then sum the results.

Similar, using a compound function:
=LOOKUP(99^99,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:256"))))

Regards,
Ryan--

Signature

RyGuy

> A1 = 1 A
> B1 = 1.5 S
[quoted text clipped - 14 lines]
> What do I need to add or change in my formula to look for the "A" in B1 and
> add 3 to my total?

Reply to this Message

Carole - 08 May 2008 21:26 GMT

Maybe I wasn't explicit enough.

I need to add all instances of "A" or "S" (not both) on a one line range of
cells. My formula needs to give me a total of 4 when I search for "A" and
1.5 when I search for "S". This formula will be copied in several cells to
give me a total for all instances of the criteria in the range I am totaling.

> Here is one option:
> Function ExtractNumber(rCell As Range) As Double
[quoted text clipped - 33 lines]
> > What do I need to add or change in my formula to look for the "A" in B1 and
> > add 3 to my total?

Reply to this Message

Carole - 08 May 2008 21:38 GMT

Those totals will be for line one. Line 2 will be 12.25 for "S".

> Maybe I wasn't explicit enough.
>
[quoted text clipped - 40 lines]
> > > What do I need to add or change in my formula to look for the "A" in B1 and
> > > add 3 to my total?

Reply to this Message