Looking for a way to convert data in an Excel cell to consistent information.

MMacygin used Ask the Experts™
I'm pulling reports from a Learning Management System on training completions.  While my team is working on modifying the backend information to deliver consistent data, I still need to report on current data coming out.  I have a column labeled "Training Duration".  The data currently being delivered can be in either Minutes or Hours.  An example of each is below:

0.75 Hours
30.00 Minutes

I'd like to examine the cell and convert all of them to A) Hours and B) a cell with just the actual time listed.  The above examples would then be turned into the following:


Is this a VB script problem?  Is there some formula in Excel that would allow me to do this?  Any assistance, suggestions or recommendations are appreciated.

An example of the data file  is attached. The column in question is highlighted.C--Users-06596.GLOBAL-Desktop-16010.xlsx
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016


pls try
=IF(RIGHT(I2,5)="Hours",LEFT(I2,SEARCH(" ",I2)-1),IF(RIGHT(I2,7)="Minutes",TEXT(VALUE(LEFT(I2,SEARCH(" ",I2)-1))/60,"0,00"),""))

Open in new window

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Or try this....
Sub ConvertDurations()
Dim lr As Long, i As Long
Dim x
lr = Cells(Rows.Count, "I").End(xlUp).Row
x = Range("I2:I" & lr).Value
For i = 1 To UBound(x)
   If InStr(x(i, 1), "Hours") > 0 Then
      x(i, 1) = Trim(Replace(x(i, 1), "Hours", "")) + 0
   ElseIf InStr(x(i, 1), "Minutes") > 0 Then
      x(i, 1) = Round(Trim(Replace(x(i, 1), "Minutes", "")) / 60, 2)
   End If
Next i
Range("I2").Resize(UBound(x), 1).Value = x
End Sub

Open in new window



This was the solution I needed.  Handled the column flawlessly.  I so appreciate the quick response and solution.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Glad to know that the solution worked as per your expectations.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial