Create custom text format?

[Jaffo]

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!

 

[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)

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

 

[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

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

 

[Jaffo]

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...

 

[Jaffo]

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.

 

[vezerid]

=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

 

[Pete]

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

 

[Jaffo]

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!