Add number before text that matches criteria
 

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?

Add number before text that matches criteria
 

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&"01234567 89")),ROW(INDIRECT("1:256"))))


Regards,
Ryan--

--
RyGuy


"Carole" wrote:

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?

Add number before text that matches criteria
 

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.

"ryguy7272" wrote:

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&"01234567 89")),ROW(INDIRECT("1:256"))))


Regards,
Ryan--

--
RyGuy


"Carole" wrote:

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?

Add number before text that matches criteria
 

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

"Carole" wrote:

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.

"ryguy7272" wrote:

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&"01234567 89")),ROW(INDIRECT("1:256"))))


Regards,
Ryan--

--
RyGuy


"Carole" wrote:

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?