Formating

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Jan 21, 2003

• #2

Assuming you want the format for display only and not to perform calculations, you can custom format your cells

00":"00":"00":"00

HTH

 

Upvote 0

B

badnazzz

New Member

Joined

Aug 30, 2005

Messages

6

• Aug 30, 2005

• #3

So I am in the same boat as eric. Yet i guess i know even less about excel.
Where do i input the 00":"00":"00":"00.
Is this a function or do I right click and select format? If so where do I input.
Also I am dealing with Keykode as well.
Which is in the format, ex. KZ41 3044 1219+10. How would I tell Excel to put in the spaces and plus sign. Thank you.

 

Upvote 0

B

badnazzz

New Member

Joined

Aug 30, 2005

Messages

6

• Aug 30, 2005

• #4

can anyone answer this?

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Aug 30, 2005

• #5

Is "KZ" a constant, or do they vary?

lenze

 

Upvote 0

B

badnazzz

New Member

Joined

Aug 30, 2005

Messages

6

• Aug 30, 2005

• #6

no the two letters will vary. and will sometimes be a question mark ie. J?32 4554 2322+01. Do you have an answer for the first question? Where do you input this timecode formatting info 00":"00":"00":"00? Thank you for your response.

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Aug 30, 2005

• #7

Select Format>Cells

On the Number Tab, Choose Custom

In the field on the right, enter the custom format
00":"00":"00":"00

lenze

Working on the second question.

 

Upvote 0

B

badnazzz

New Member

Joined

Aug 30, 2005

Messages

6

• Aug 30, 2005

• #8

thankyou thankyou thankyou. That is awesome - just put in the timecode formatting. It saves me so much not having to type that pesky ":". You are the BEST. If you can come up with something for the KeyKode you will be the BESTEST. Thank you again.

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Aug 31, 2005

• #9

To format your KeyKode, you need VBA. Assuming your codes are in Column A, starting at Row 2, this should work.

Option Explicit

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub 'column of interest
    If Target.Row < 2 Then Exit Sub 'lowest row
    If Target.Cells.Count > 1 Then Exit Sub 'no multiple selections
    With Target
        'ex: KZ12 3456 7890+12
        Application.EnableEvents = False
        .Value = Left(.Value, 4)  & " " & Mid(.Value, 5, 4) & " " & Mid(.Value, 9, 4) & "+" & Right(.Value, 2)
        Application.EnableEvents = True
    End With
End Sub

This code goes in the WorkSheet Module. Right click on the sheet tab and choose view code. Paste the code in the white panel.

HTH

lenze

 

Upvote 0

B

badnazzz

New Member

Joined

Aug 30, 2005

Messages

6

• Sep 21, 2005

• #10

OK I LIKE IT. Is there a way to make it 7th column (G) and 4th row. If not that is ok. Thank you once again for being on your game.

 

Upvote 0