Create custom text format?

Try this:

1) Highlight the column
2) Select Format | Cells
3) Pick "Custom"
4) In the "Type" box, enter 00-00-00-00-00-00

For the "O" vs 0 problem, use Find | Replace

"Jaffo" wrote:

>
> Hi everyone,
>
> I'm trying to find and easy way to format a column of alphanumeric
> text. Here's my situation.
>
> My boss sends me these excel spreadsheets of data that I need to enter
> into our DHCP server. Amongst the data is a column of ethernet
> addresses. The problem is that our DHCP server expects these ethernet
> addresses to have a colon between each 2 characters, and I always get
> the data as 12 alphanumeric characters with no seperators. Sometimes
> the list is quite extensive and it is cumbersome to manually add colons
> between each 2 digits
>
> (for those not familiar with ethernet addresses the format is
> xx:xx:xx:xx:xx:xx where x is any hexadecimal character 0-9 or a-f)
>
> It would be nice if I could just highlight that column and create a
> custom format that will just stick a colon after each 2 digits, but not
> one at the end. I couldn't figure out how to define this, though.
>
> It would be really nice if I could have it check to make sure the
> characters are within the hexadecimal limts (often someone will stick
> the letter O in instead of the number zero) and maybe put a semicolon
> ( at the end (since that's what our dhcp server expects as well.)
> But I'd be happy with just the first option, if that's possible.
>
> Can anyone help?
>
> Thanks!
>
>
> --
> Jaffo
> ------------------------------------------------------------------------
> Jaffo's Profile: http://www.excelforum.com/member.php...o&userid=30903
> View this thread: http://www.excelforum.com/showthread...hreadid=505768
>
>

Originally Posted by BekkiM

Try this:

1) Highlight the column
2) Select Format | Cells
3) Pick "Custom"
4) In the "Type" box, enter 00-00-00-00-00-00

I tried this, but it had no effect on the format at all.

I used a fake ethernet address of a1b2c3d4e5f6, and it stayed exactly like that when I put in your suggested solution.

Hi,

Kostis has supplied the correction necessary for my earlier formula
(thanks, Kostis) - sorry, I rushed it.

Custom formatting won't work because you are dealing with text values.

Pete

Thanks a lot everyone!

That last one worked perfectly for me!

I really appreciate the help - this'll save me a lot of manual data massaging!