Working with mac addresses in Excel

[Jim Scheffler]

Hello group,

I work mostly with mac addresses in spreadsheets and have come across a
formatting problem that I just can't figure out.

I receive text files with a list of hundreds of mac address in them in the
xxxx.xxxx.xxxx format. I have a VBA script that imports the file into a
spreadsheet, cleans up leading and trailing text adjusts column widths and
saves it as a .csv file.
Everything was working fine until someone needed this report with the mac
addresses in the xxxxxxxxxxxx format. I tried find and replace the "." with
"" which works in most cases except for mac addresses that start with
000x.xxxx.xxxx or addresses that have an "e" towards the end,
1234.5678.91e8. Excel turns this into a Scientific notation entry.
I import the text file data as text but when Excel removes the "." it seems
to ignore this formatting.
Any help on this would be great,

Thanks
Jim

 

[Tom Ogilvy]

You could convert it with a macro

Sub Tester2()
Dim sStr As String
Dim rng As Range, cell As Range
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
For Each cell In rng
sStr = Application.Substitute(cell, ".", "")
cell.Value = "'" & sStr
Next

End Sub

 

[Tom Ogilvy]

I believe if you look at the CSV file, it will be OK. However, when Excel
brings it back in, it treats those values as numbers. The easiest way to
overcome this is to change the extension on the file to .Txt rather than
..CSV, then you will get the text import wizard when you open it. Selected
Delimited, with comma as the delimiter and in the last dialog, you can
specify how to treat each column. Mark the column as Text.

You can record a macro while you do this manually, then you can run the
macro to bring in the file (renaming it with a .txt extension) and the macro
will use the settings you chose in the wizard without opening the wizard.
If you leave it as .csv, then excel will ignore your settings and make its
own interpretation - it is pretty stubborn when it comes to .csv files.

Regards,
Tom Ogilvy

 

[Jim Scheffler]

Thanks Tom,
That did the trick.

Thanks for all your help,

Jim