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.

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

LetterOut = LetterOut & Mid(rng.Value, i, 1)
End Select
Next i
End Function
=============
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 like
any number. If you normally would multiply hours by pay rate in B2, and
the mixed code PRS8 is in A2, this formula would do it:

=LetterOut(A2) * B2


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48776

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/





On 10 jan, 05:07, TEK wrote:
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.

Works great! Thanks much.

"JBeaucaire" wrote:


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

LetterOut = LetterOut & Mid(rng.Value, i, 1)
End Select
Next i
End Function
=============
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 like
any number. If you normally would multiply hours by pay rate in B2, and
the mixed code PRS8 is in A2, this formula would do it:

=LetterOut(A2) * B2


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48776