locked
Concatenate whole columns while switching between TEXT/NUMBER formats RRS feed

  • 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!



    • Edited by Bandru Thursday, December 3, 2020 6:59 PM
    Thursday, December 3, 2020 6:45 PM

All replies

  • 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)

    Thursday, December 3, 2020 8:40 PM
  • 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!

    Friday, December 4, 2020 2:52 AM