Hello group,

Anyone have experience working with mac addresses in Excel
2002 either with VBA or worksheet functions?
Here's my problem, I import text and csv files with hundreds of mac
addresses in to spreadsheets and run reports on them doing VLOOKUPs and so
on.
The problem is when a mac starting with 000 and has all numbers in it the
first two or three zeros get cut off, ie mac 000123456789 ends up 123456789
after import. Also sometimes Excel will format the mac as scientific
notation if the mac ends with zeros, ie mac 000123456700 ends up 1.23E+08.
I've tried formating the mac column I'm importing as text in the import
wizard but it still formats some macs as above. I've tried to format the
column as text in my spreadsheet before I import the macs but still I get
some macs that don't import correctly.
Any ideas or web links would be greatly appreciated.

Jim Scheffler

Jim

If you have a choice, don't use csv's as Excel sees that as standard file
format and opens how it wants.

Instead try and import .txt or files with no extension and then use the
third part of the wizard to mark the MAC addresses as text. This will stop
your zero stripping and scientific notation issues

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Jim Scheffler" wrote in message
...
Hello group,

Anyone have experience working with mac addresses in Excel
2002 either with VBA or worksheet functions?
Here's my problem, I import text and csv files with hundreds of mac
addresses in to spreadsheets and run reports on them doing VLOOKUPs and so
on.
The problem is when a mac starting with 000 and has all numbers in it the
first two or three zeros get cut off, ie mac 000123456789 ends up
123456789
after import. Also sometimes Excel will format the mac as scientific
notation if the mac ends with zeros, ie mac 000123456700 ends up 1.23E+08.
I've tried formating the mac column I'm importing as text in the import
wizard but it still formats some macs as above. I've tried to format the
column as text in my spreadsheet before I import the macs but still I get
some macs that don't import correctly.
Any ideas or web links would be greatly appreciated.

Jim Scheffler

In the text file, put quotes, or single quotes around the number "000... or
'000...
or, try a single quote inside a quote, like " ' 000...

If you rename the *CSV files to *TXT they will open in the text import
wizard, if you click next twice to go to step 3 you can set the import as
text under column data format, then click finish


Regards,

Peo Sjoblom


"Jim Scheffler" wrote:

Hello group,

Anyone have experience working with mac addresses in Excel
2002 either with VBA or worksheet functions?
Here's my problem, I import text and csv files with hundreds of mac
addresses in to spreadsheets and run reports on them doing VLOOKUPs and so
on.
The problem is when a mac starting with 000 and has all numbers in it the
first two or three zeros get cut off, ie mac 000123456789 ends up 123456789
after import. Also sometimes Excel will format the mac as scientific
notation if the mac ends with zeros, ie mac 000123456700 ends up 1.23E+08.
I've tried formating the mac column I'm importing as text in the import
wizard but it still formats some macs as above. I've tried to format the
column as text in my spreadsheet before I import the macs but still I get
some macs that don't import correctly.
Any ideas or web links would be greatly appreciated.

Jim Scheffler