Ignore text but count values in same cell

[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.

 

[JBeaucaire]

Open the VBEditor (Alt-F11
Insert a Modul
Paste the following new function into the module
============
Function LetterOut(rng As Range
Dim i As Intege
For i = 1 To Len(rng
Select Case Asc(Mid(rng.Value, i, 1)
Case 0 To 64, 123 To 19

LetterOut = LetterOut & Mid(rng.Value, i, 1
End Selec
Next
End Functio
============
Press Alt-Q to close the Editor

Now you've added the function LetterOut. Use it like so

=LetterOut(A1

...to return the numbers only from cell A1. Now it can be treated lik
any number. If you normally would multiply hours by pay rate in B2, an
the mixed code PRS8 is in A2, this formula would do it

=LetterOut(A2) * B

 

[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/

 

[TEK]

Works great! Thanks much.