Create custom text format?

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!

Assuming that the text is in column A from cell A1 onwards, put this
formula in B1:

=LEFT(A1,2)&":"&MID(A1,4,2)&":"&MID(A1,6,2)&":"&RIGHT(A1,2)

and copy this down. You can fix the values if necessary.

One easy way to change O to 0 is to highlight column A and do Edit |
Replace.

Hope this helps.

Pete

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 Pete

Assuming that the text is in column A from cell A1 onwards, put this
formula in B1:

=LEFT(A1,2)&":"&MID(A1,4,2)&":"&MID(A1,6,2)&":"&RIGHT(A1,2)

Ok I made a test spreadsheet and tried this out
I put a fake ethernet address in a1 and copied it down several rows
the address was:
a1b2c3d4e5f6

I put your formula in B1 and copied it down and I got this result:
a1:2c:3d:f6

So, we're almost there - I don't know enough to fix the formula myself...

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.

=LEFT(A1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&MID(A1,11,2)

HTH
Kostis Vezerides

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!