Counting across a row with text in the value

  • Thread starter Thread starter Grace
  • Start date Start date
G

Grace

Hello, how do I count across a row when the values contain
both text and a number. For example, one row may contain
the following 5 columns

..5 FTO, FTO, FT0, .5 FTO, .5 FTO

The (sum?) across the row should be calculated to 2.5 FTO.

Thank you,
Grace
 
2.5? I get 1.5 with:

=SUM(IF((COUNTIF(A1:E1,"*FTO*"))*(ISNUMBER(1*LEFT(TRIM
(A1:E1),FIND(" ",TRIM(A1:E1))-1))),1*LEFT(TRIM(A1:E1),FIND
(" ",TRIM(A1:E1))-1)))

Array-entered (meaning press ctrl/shift/enter after
inserting the formula).

HTH
Jason
Atlanta, GA
 
-----Original Message-----
Hello, how do I count across a row when the values contain
both text and a number. For example, one row may contain
the following 5 columns
..5 FTO, FTO, FT0, .5 FTO, .5 FTO
Click to expand...
Grace ...
This might help you!

IF and ONLY IF the legal value comes before the text and
is separated by a space:

"Val" returns the value of the entire numeric prefix, if
any, else returns 0.

Val(".5 FTO") returns 0.5
Val("FTO") returns 0

Test Snippet:

Dim ir Long ' Row index
Dim ic Long ' Column index
Dim sum Long ' Accumulator

sum = val(Cells(ir, ic)) _
+ val(Cells(ir, ic + 1)) _
+ val(Cells(ir, ic + 2)) etc.

Of course, you should do this in a loop.

Good luck with your efforts!

Blessings in abundance, all the best, and ENJOY!

Art Du Rea Carlisle, PA USA
 
Back
Top