2

The other day I had some timesheet information in a spreadsheet ($X/hr, Y hrs), and I wanted to compute dollars spent. I wound up just editing out the "/hr", " hrs" and "$" characters, but is there a way I could have converted these cells directly to floats in the formula to perform the calculation?

=B$3 * B$4

I tried VALUE and it didn't work.

3
  • I answered something similar here: superuser.com/questions/794199/… Read the comments. If you would like help with formulas that work for your data let me know...
    – Tyson
    Aug 9, 2014 at 6:02
  • Find and replace the "/hr" string and "hrs" with nothing "" using the Find replace dialog box in excel. In the title column of the time sheet add the units. For example "Rate ($/hr)" and "Time (hrs)". Now they are just mere numbers in that column. U can use simple * to do the job. This is how we do timesheets in my office.
    – Prasanna
    Aug 9, 2014 at 6:40
  • @Prasanna That was what I did - but I was hoping to find a programmatic way to do this. The answer below was more what I was looking for. Aug 9, 2014 at 16:12

3 Answers 3

4

Try this small UDF

Public Function NumberPart(s As String) As Double
    Dim s2 As String, i As Long, L As Long, CH As String
    s2 = ""
    L = Len(s)
    For i = 1 To L
        CH = Mid(s, i, 1)
        If CH Like "[0-9]" Or CH = "." Then
            s2 = s2 & CH
        End If
    Next i
    NumberPart = CDbl(s2)
End Function

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=NumberPart(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

1
1

Although the above answer works and very well documented, I have two objections against it:

  • It gives false output if the input contains unintended numerical values (for example, try: "12 hrs1" or "$ 12 / 1 hr" or when your locale uses "," as a decimal separator instead of ".").
  • If possible I'd stay away from Visual Basic for Applications (VBA) and UDF's; especially if the same can be achieved with the following native Excel functions:
    • LEFT: takes the left side of a string up to a certain length (used to cut the "/hr" off).
    • VALUE: converts a tekst to a value (used to convert the remaining value in a string to a value).
    • FIND: searches for a character or string in another string (used to search for '/' in "/hr").
    • SUBSTITUTE: changes all occurrences of a string in another string (used to remove the "HRS").
    • UPPER: converts all characters to uppercase (used to change "Hrs" and "hrs" etc. to "HRS").

If the mentioned values are in column A (price per hour) and B (amount of hours), starting with data at row 1 (no header row) then put the following formula's in Excel:

  • C1: =VALUE(LEFT(A2;FIND("/";A1;1)-1)) and copy-paste down as needed.
  • D1: =VALUE(SUBSTITUTE(UPPER(B1);"HRS";"")) and copy-paste down as needed.

That should do the trick. Nothing too complex I'd say.

2
  • Good ideas___________another approach is to put the units in the formatting and avoid the numerical problems entirely! Aug 10, 2014 at 12:20
  • Agree, but that's not an answer to the OP's question.
    – agtoever
    Aug 10, 2014 at 12:30
0

Another method is to use VBA together with Regex and a fitting pattern.
For example, the \D pattern matches everything which isn't a digit (0-9).

  1. Insert this code into a VBA module (ALT+F11). The UDF needs 2 arguments when called from Excel: The input cell and the RegEx pattern to check against. Then, the code removes every character which matches the given pattern.

    Function Remove(objCell As Range, strPattern As String)
    
        Dim RegEx As Object
        Set RegEx = CreateObject("VBScript.RegExp")
        RegEx.Global = True
        RegEx.Pattern = strPattern
        Remove = RegEx.Replace(objCell.Value, "")
    
    End Function
    
  2. Back in Excel write your formula in this syntax to a new cell

    =Remove( <cellToCheck> ,"<regExPattern>")
    

I've taken your example to multiply two "non-digit" cells: $50/hr * 8 hrs = 400

enter image description here


However, this won't work if you have multiple (unintended) numerical values in one cell e.g $50/hr2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .