text formatting problems

[mdf]

I receive spreadsheets from remote site support staff containing Ethernet
addresses in a column. Often these are entered with spaces, hyphens, etc.
between each pair. However, I need them to be 12 character strings, no spaces
or other chars. The column is formatted as text, and I do a replace on the
problem character. This works unless the address could be interpreted as an
exponent; i.e 00-50-04-82-1E-20 becomes 5.00E+26 when I replace the hyphens
with nothing. In addresses that do not contain alpha chars, the leading zeros
are dropped.

Has anyone else seen this? How do you get around it?

 

[Paulw2k]

Take the E out first, then the hyphens.
Also format the cells to "000000000000" (12 zeros).

Regards

Paul

 

[Frank Stone]

hi,
you did not say how you were removing the hyphens so i am
assuming you use a formula like substitute or whatever.
when you do that add a apostrophe nestled in double quotes
("'") to the group as the first character. it will not
show up in the cell but it will cause the cell contents to
be treated as text. it will solve your leading zero
problem and your other problem as well.
Regards
Frank