Concatenate whole columns while switching between TEXT/NUMBER formats

Question

Hi, I would like to use VBA to convert data in a timespan column (listed as TEXT) into a timespan format that is useable for me. (until the last data point in Column B---and I don't have to worry about blank cells)

I start with a TEXT value that looks like this in Column B

00:00:04.620 --> 00:00:12.630

So far, I have successfully separated the data into two columns (Column F and Column G) with the following:

Sub CopyColumnTextTime()  'copies column B into columns E & F
Sheets("sheet2").Columns("B").Copy Sheets("sheet2").Columns("E:F")
End Sub

Sub ReplaceStart() 'shows only START times by deleting arrow and everything after
Sheets("Sheet2").Columns("E").Replace What:=" *", Replacement:=""
End Sub

Sub ReplaceEnd() 'shows only END times by deleting arrow and everything before
Sheets("Sheet2").Columns("F").Replace What:="* ", Replacement:=""
End Sub

Sub FormatTimes()  'rounds the numbers to one decimal.  I need the numbers to be rounded here instead of just truncated.
Sheets("Sheet2").Columns("E:F").NumberFormat = "h:mm:ss.0"
End Sub

Which gets me Start and End times in Columns F & G respectively with the (rounded) number of decimals I need.  This is good so far, but they are now converted to numbers.

0:00:04.6

0:00:12.6

Since I next need to concatenate the data into one column that it looks like this 0:00:04.6/0:00:12.6 I'm running into trouble when using the NUMBER values.

Thus, when I try I get this: 5.34722222222222E-05/0.000146180555555556.

I'm guessing I need to convert the rounded times in the step above to strings first, but I can't piece this last step together myself.  

I'm a beginner with VBA, so I know the codes I already showed above are not optimal, but I would like to keep those as is if possible, so that I can maintain my minimal understanding of what I have so far in order to learn this first.

Any tips on how to make an additional step for finishing my work???  THANKS!

Answer 1

Something like

    With Sheets("Sheet2").Range("H:H")
        .Formula = "=TEXT(E1,""hh:mm:ss.0"")&""/""&TEXT(F1,""hh:mm:ss.0"")"
        .Value = .Value
    End If

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 2

Thanks for your reply!  I'm seeing how this will be helpful, but it seems to be getting caught up on not having an If statement to start.

I'm going to hit the drawing boards with this and try to puzzle the rest out though!