Cell Format won't hold

Toshiba / Satellite pro l300
February 27, 2009 at 12:27:39
Specs: Microsoft Windows Vista Business, 2 GHz / 4 gigs
Trying to format some cells in Excel 2002.

I have a customized number format that is ##-##-##-##-##-##-##. It's for mac addresses for wireless adapters I hand out.

However, no matter how I try, I can not get the cells to hold the format. They just go to a regular string of numbers such as: 123456.

Just found that if I do ONLY numbers, it works, except if the number begins with 00's (zeros) it drops the zeros. and leaves the dash. (-22-34-etc.) but it won't do any formating at allif there are letters in the string. Anyway I can do this? 00-22-4e-5t-09?

I know I'm missing something simple, but for the life of me can't figure out what.

Who in their right mind would ever need more than 640K of RAM!?
Bill Gates, 1981


See More: Cell Format wont hold

Report •


#1
February 27, 2009 at 13:05:14
In Custom formatting:

# Digit placeholder. This code does not display extra zeros.

0 (zero) Digit placeholder. This code pads the value with zeros to fill the format.

00-00-00-00-00-00-00

MIKE


Report •

#2
February 27, 2009 at 13:23:35
That worked. Thanks.

Now, can I do anything about having letters show up in formating?

Who in their right mind would ever need more than 640K of RAM!?
Bill Gates, 1981


Report •

#3
February 27, 2009 at 14:00:13
You'll have to elaborate a bit...

MIKE


Report •

Related Solutions

#4
February 27, 2009 at 14:08:34
OK. Sorry. Should have been more specific.

I have a series of numbers, MAC addresses actually, I'd like to have the cell format like this: 00-2e-44-5k-6t-88.

The letters can appear anywhere as you know. And the string may not always start with 00's.

I'm trying to keep from have to type in the dashes.

I'm a lazy old dog.

Who in their right mind would ever need more than 640K of RAM!?
Bill Gates, 1981


Report •

#5
February 27, 2009 at 14:44:15
As far as I know it can't be done with Custom Formatting. Excel never claimed to be a text editor, so it's text formatting options are limited.

I can think of 2 work-arounds:

1 - Put your 10 character string in A1 and put this in another:

=LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2)&"-"&RIGHT(A1,2)

Note 1: You might have to use the 00-00-00-00-00 custom format on the original data to retain the leading zeros.

Note 2: You might have to enter the single quote text character before the string if there are too many leading zeros in the data.

2 - You could use a Worksheet_Change macro if you want to keep the data in the same cell as entered, but you might have to same issue with leading zeros as in my other suggestion.

For example, if the data will be entered in column A, right click the sheet tab and paste in this code.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
  If Target.Column = 1 Then
   MyMAC = Target
     NewMAC = Left(MyMAC, 2) & "-" _
            & Mid(MyMAC, 3, 2) & "-" _
            & Mid(MyMAC, 5, 2) & "-" _
            & Mid(MyMAC, 7, 2) & "-" _
            & Right(MyMAC, 2)
      Target = NewMAC
  End If
 Application.EnableEvents = True
End Sub


Note: This code does not check to see if the data is 10 characters or anything like that, so it's going to try and format whatever you type into Column A. That can be fixed, but it takes a little more code.


Report •

#6
February 27, 2009 at 14:47:39
BTW...I might be a little short on the number of digits in a MAC address, but obviously the concept is the same. Just expand the formula or the code.

Report •

#7
February 27, 2009 at 15:22:32
Jeez, thank you so much!

Wow, all that just so I don't have to type hyphens.

Almost embarrassed to say, I'll just type them in.

But your efforts and time are greatly appreciated.

Thank you.

Who in their right mind would ever need more than 640K of RAM!?
Bill Gates, 1981


Report •

#8
February 27, 2009 at 15:41:59
I'm not sure why you would type them in when the solutions have been offered.

If you got one or two, then sure, but if it's something you need to do many, many times, then one shot at automating the process makes sense to me.

Obviously, it's your call.


Report •

#9
February 27, 2009 at 15:58:14
I only do about 2 a month.

But I may try your solution so I can learn.

Thanks again.

Larry

Who in their right mind would ever need more than 640K of RAM!?
Bill Gates, 1981


Report •

#10
February 27, 2009 at 17:09:45
re: ...so I can learn

Now that's the spirit! ;)


Report •


Ask Question