VBA - Convert text to number

[ebilbrough]

I have a range of text (B3:AD1500) that is in text format. All the text format cells represent hours worked and have an "h" at the end (ex. 23.25h, 0.00h).

I'm looking for a way in VBA to have the "h"dropped and convert the text to a number.

Thanks in advance.

 

[JeanKim]

23.25 <=== 23 Hours and 25 minute? or 23.25 minute?

 

[ebilbrough]

.25 hours (15 minutes)

 

[Jonmo1]

Try using Find/Replace to replace the h with nothing.

 

[ebilbrough]

I need the result to also convert the cell to a number format. Is this possible?

 

[shg]

Before:

	B​	C​	D​	E​	F​	G​	H​
3​	7.85h	1.98h	16.23h	18.37h	14.57h	20.77h	12.24h
4​	1.95h	12.24h	22.81h	18.14h	11.15h	6.18h	14.81h
5​	12.54h	11.40h	16.70h	9.74h	4.12h	16.40h	17.65h
6​	3.94h	2.89h	11.62h	12.74h	14.52h	1.95h	23.97h
7​	16.37h	5.75h	7.23h	18.84h	4.18h	20.69h	9.22h
8​	12.74h	4.51h	14.52h	20.63h	5.30h	5.25h	20.65h

Code:

Sub eb()
  With Range("B3:AD1500")
    .Replace "h", ""
    With Cells(Rows.Count, Columns.Count)
      .Value = 24
      .Copy
    End With
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide
    .NumberFormat = "[h]:mm"
    Cells(Rows.Count, Columns.Count).Clear
  End With
  ActiveSheet.UsedRange
End Sub

After:

	B​	C​	D​	E​	F​	G​	H​
3​	7:51​	1:58​	16:13​	18:22​	14:34​	20:46​	12:14​
4​	1:57​	12:14​	22:48​	18:08​	11:09​	6:10​	14:48​
5​	12:32​	11:24​	16:42​	9:44​	4:07​	16:24​	17:39​
6​	3:56​	2:53​	11:37​	12:44​	14:31​	1:57​	23:58​
7​	16:22​	5:45​	7:13​	18:50​	4:10​	20:41​	9:13​
8​	12:44​	4:30​	14:31​	20:37​	5:18​	5:15​	20:39​

 

[Jonmo1]

Something like this (obtained mainly from the macro recorder while doing it by hand)

Sub Macro1()
With Range("A1:A10")
    .Replace What:="h", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .NumberFormat = "0.00"
End With
End Sub

 

[ebilbrough]

Many thanks Jonmo1 & shg - just what I was looking for.

 

[shg]

You're welcome.

 

[Jonmo1]

You're welcome.