#1
|
|||
|
|||
HELP! … I have a column in a spreadsheet that is formatted as follows
HELP! … I have a column in a spreadsheet that is formatted as follows:
2 H, 15 M 12 H, 9 M 12 H, 15 M 2 H, 9 M … I need to convert it to hours as: 2.25 12.15 12.25 2.15 … Ideas? |
#2
|
|||
|
|||
This seems to work
Code:
Option Explicit Sub ConvertStringToTime() 'Converts a string into a useable decimal type format Dim CheckRow As Long, CheckString As String, FinalString As String Dim HourString As String, MinuteString As String, TotalRows As Long Dim TimeResult As String, UseColumn As String, ResultColumn As String Dim MinuteConvert As Single, BadData As Variant, x As Integer BadData = Array(" ", "H", "M", "h", "m", ",") TotalRows = Range("A50000").End(xlUp).Row UseColumn = "A" 'Change this to whatever column your data is in ResultColumn = "B" 'Change this to whatever Column you want the result. TotalRows = Range(UseColumn & "50000").End(xlUp).Row For CheckRow = 2 To TotalRows 'Start at 2 if you have headers otherwise change to 1 CheckString = Range(UseColumn & CheckRow).Value 'Separate hours and minutes HourString = Mid(CheckString, 1, InStr(1, CheckString, ",") - 1) MinuteString = Mid(CheckString, InStr(1, CheckString, ",")) 'Remove unusable data For x = 0 To UBound(BadData) HourString = Replace(HourString, BadData(x), "") MinuteString = Replace(MinuteString, BadData(x), "") Next x MinuteConvert = 100 / (60 / MinuteString) FinalString = HourString & "." & MinuteConvert 'Using string to keep decimal points down Range(ResultColumn & CheckRow).Value = FinalString Next CheckRow MsgBox "Complete" End Sub Last edited by excelledsoftware; 08-21-2014 at 09:30 PM. Reason: comment typo |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
My documents have been re-formatted??? | Davec666 | Word | 2 | 04-30-2013 08:40 AM |
Pre-formatted label printer | jdorin | Mail Merge | 7 | 04-03-2013 12:14 AM |
Automatically enter date into a column and make that column read only | Mr Davo | Excel | 1 | 10-29-2012 01:07 AM |
Can I change the horizontal scrollbar to scroll smoothly rather than column by column | carpat | Excel | 0 | 01-10-2012 09:34 AM |
Odd behaviour in formatted cells | furface00 | Excel | 3 | 03-11-2011 08:02 AM |