Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Name: seawatch
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
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
0 
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
0
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
0
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.
0
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.
0
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
0
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.
0
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
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
