Solved: Help needed applying ":" between numbers

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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
 

Attachments

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

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!
 

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!
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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?
 

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.
 

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.
 

Attachments

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.
 

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.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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.
 

Attachments

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
If it works as you thought then don't forget to mark your post as solved
Happy coding
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top