Working with mac addresses in Excel
 

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

Working with mac addresses in Excel
 

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



--
Regards,
Tom Ogilvy



"Jim Scheffler" wrote in message
...
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

Working with mac addresses in Excel
 

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" wrote in message
...
Tom,

That worked great, Thanks
Problem now is when I or the script save this worksheet as a .csv file and
then reopen the .csv file some of the macs are back to the Scientific
notation format. Any way different I can save the .csv file to keep the
formatting we created in the original worksheet?

Thanks again,
Jim

"Tom Ogilvy" wrote in message
...
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



--
Regards,
Tom Ogilvy



"Jim Scheffler" wrote in message
...
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

Working with mac addresses in Excel
 

Thanks Tom,
That did the trick.

Thanks for all your help,

Jim
"Tom Ogilvy" wrote in message
...
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" wrote in message
...
Tom,

That worked great, Thanks
Problem now is when I or the script save this worksheet as a .csv file
and
then reopen the .csv file some of the macs are back to the Scientific
notation format. Any way different I can save the .csv file to keep the
formatting we created in the original worksheet?

Thanks again,
Jim

"Tom Ogilvy" wrote in message
...
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



--
Regards,
Tom Ogilvy



"Jim Scheffler" wrote in message
...
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