Ignore text but count values in same cell

T

TEK

I'm looking for a formula that ignores text, but counts numerical values
within the same cell. This formula applies to a spreadsheet that tracks
vacation/sick time by entering a letter and numeric value. For example,
entering V6 in a cell would mean this particular individual has taken 6 hours
of vacation time. Not all codes are prefaced with a single letter, however.
Such as PRS8, which would equate to 8 hours of personal time. Any help would
be appreciated.
 
J

JB

1-If one number only to right:
=VALUE(RIGHT(A1,1))

2-UDF in a module:

Function NumChaine(chaine)
temp = ""
For i = 1 To Len(chaine)
c = Mid(chaine, i, 1)
If c >= "0" And c <= "9" Then temp = temp & c
Next i
NumChaine = Val(temp)
End Function

=NumChaine(A1)

3- SUBSTITUTE PRS and V with empty string:

=VALUE(SUBSTITUTE(SUBSTITUTE(A1,"PRS",""),"V",""))

http://cjoint.com/?bkj256aD68

JB
http://boisgontierjacques.free.fr/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ignore text in timecard calculation 1
sum and count text and numbers same column 1
Count of numerical values within a cell 5
creating formulas which ignore text in the relevant cells 3
Fill background color automatically by typing a letter in a cell 3
Using text values in Pivot Tables 5
adding cells with text and numbers 1
Ignore Text when calculating time 1

Top