1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Help needed applying ":" between numbers

Discussion in 'Business Applications' started by Vichama, Apr 26, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Vichama

    Vichama Thread Starter

    Joined:
    Feb 19, 2009
    Messages:
    21
    Hi,

    I have been searching the internet for a couple of hours a day and found nothing that helps as my request is a little too specific. I have over 6000 entries in a sheet where data has to be applied so need it to be as quick as possible.

    The format I need the data to be presented in would be 00:00:00:00. Excel doesn't allow for this as it is not time in the 24 hour clock, it is not a date etc. I have looked at a number of LEN options but to no avail. Custom cell formatting doesn't support this format either

    I am using columns A:I This format needs to be applied to columns F,G,H.

    I need to be ale to enter something like 10233400 to a cell in each of those columns and for a macro to count every 2 numbers and add a : so it looks like 10:23:34:00

    Any suggestions would be greatly appreciated!

    Thanks
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try this

    Function Reformat(tstring As Variant) As String
    Dim ttemp As Variant
    x = 1
    Do While x < Len(tstring)
    ttemp = ttemp & Mid(tstring, x, 2)
    x = x + 2
    If x < Len(tstring) Then ttemp = ttemp & ":"
    Loop
    Reformat = ttemp
    End Function

    I attached a sheet with the code included
     

    Attached Files:

  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This will format the active cell, you could change it to use the Worksheet Change event to trigger it instaed of running the macro manually.
    ActiveCell.Value = Format(ActiveCell, "00:00:00:00")
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry Keebellah I didn't see you post as I was typing mine. :)
     
  5. Vichama

    Vichama Thread Starter

    Joined:
    Feb 19, 2009
    Messages:
    21
    Hi Keebellah,

    Thanks for the quick response, I am note sure how to get your suggestion to work in my situation?

    In column B I have a list of items from cell 2 - 6199, I would need to enter a number in to F2, that would convert from 10233400 to 10:23:34:00. Then a different number in G2 that would do the same and then finally a different number in H2 that would do the same.

    This process would then repeat for F3,G3,H3....so on and so forth.

    Thanks again!
     
  6. Vichama

    Vichama Thread Starter

    Joined:
    Feb 19, 2009
    Messages:
    21
    Hi OBP,

    Thanks for the reply, would you mean it to look something like this:

    Private Sub worksheet_Change(ByVal Target As Range)

    ActiveCell.Value = Format(ActiveCell, "00:00:00:00")

    End Sub

    Thanks!
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi OBP, thta happens, we are all looking and trying.
    Your suggestion does the job too.
    Vichama, If you put attach a sample file we could take a look.
    You could use the On Change method as OBP has said and then just check if it's the cells you need to change or just run an macro that checks the necessary cells and if the conversion has not been done format these too?
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Vichama, yes but you might like add a check that the activecell is actually in the ranges that you want it to work on.
     
  9. Vichama

    Vichama Thread Starter

    Joined:
    Feb 19, 2009
    Messages:
    21
    Hi,

    I have attached an example sheet where row 2 shows what I would like the sheet to look like, but without the manual entry of the : as I have over 6000 entries looking for something a little quicker?

    Thanks for all your help.
     

    Attached Files:

  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The version that I posted was for Manual Entry only.
    You need a for/next loop to apply that line of code to each cell in the Columns F, G & H.
    I wil post something shortly.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Try this version, the Macro is initiated by ctrl+q from the Worksheet that you want to modify.
    Remember to take a backup copy of your workbook before running it on yours.
     

    Attached Files:

  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    We're all looking at the challenge

    I added some on change and als use the reformat funtion I put together

    What the sheet expects is that you enter the value in F and the value in H
    If both are fillen G is calculated and then all are formatted

    Maybe a long shot but if it works?

    It only checks if the range is in F, G and or H and if F ans H are not formatted yet.
     

    Attached Files:

  13. Vichama

    Vichama Thread Starter

    Joined:
    Feb 19, 2009
    Messages:
    21
    Thanks guys, that's amazing does exactly what I need it to do.

    Thanks ever so much for your help, hope you both have a good day.
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    If it works as you thought then don't forget to mark your post as solved
    Happy coding
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919311

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice