Ignore text but count values in same cell
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. |
Ignore text but count values in same cell
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 |
Ignore text but count values in same cell
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. |
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 |
All times are GMT +1. The time now is 03:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com