Posted to microsoft.public.excel.misc
|
|
Ignore text but count values in same cell
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
|