software

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: www.excelforum.com/member.php...oamp;userid=30903
View this thread: www.excelforum.com/showthread...hreadid=505768Assuming that the text is in column A from cell A1 onwards, put this
formula in B1:

=LEFT(A1,2)amp;quot;:quot;amp;MID(A1,4,2)amp;quot;:quot;amp;MID(A1,6,2)amp;quot;:quot;amp;RI GHT(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.

PeteTry this:

1) Highlight the column
2) Select Format | Cells
3) Pick quot;Customquot;
4) In the quot;Typequot; box, enter 00-00-00-00-00-00

For the quot;Oquot; vs 0 problem, use Find | Replace

quot;Jaffoquot; wrote:

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


Pete Wrote:
gt; Assuming that the text is in column A from cell A1 onwards, put this
gt; formula in B1:
gt;
gt; =LEFT(A1,2)amp;quot;:quot;amp;MID(A1,4,2)amp;quot;:quot;amp;MID(A1,6,2)amp;quot;:quot;amp;RI GHT(A1,2)
gt;

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
------------------------------------------------------------------------
Jaffo's Profile: www.excelforum.com/member.php...oamp;userid=30903
View this thread: www.excelforum.com/showthread...hreadid=505768
BekkiM Wrote:
gt; Try this:
gt;
gt; 1) Highlight the column
gt; 2) Select Format | Cells
gt; 3) Pick quot;Customquot;
gt; 4) In the quot;Typequot; box, enter 00-00-00-00-00-00
gt;
gt;

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.--
Jaffo
------------------------------------------------------------------------
Jaffo's Profile: www.excelforum.com/member.php...oamp;userid=30903
View this thread: www.excelforum.com/showthread...hreadid=505768=LEFT(A1,2)amp;quot;:quot;amp;MID(A1,3,2)amp;quot;:quot;amp;MID(A1,5,2)amp;quot;:quot;amp;MI D(A1,7,2)amp;quot;:quot;amp;MID(A1,9,2)amp;quot;:quot;amp;MID(A1,11,2)

HTH
Kostis VezeridesHi,

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!--
Jaffo
------------------------------------------------------------------------
Jaffo's Profile: www.excelforum.com/member.php...oamp;userid=30903
View this thread: www.excelforum.com/showthread...hreadid=505768