Working with mac addresses

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


0
Jim1764 (1)
1/26/2005 4:53:54 PM
excel.misc 78881 articles. 3 followers. Follow

3 Replies
153 Views

Similar Articles

[PageSpeed] 16

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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Jim Scheffler" <Jim1764@charter.net> wrote in message 
news:u1vyze8AFHA.2676@TK2MSFTNGP12.phx.gbl...
> 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
>
> 


0
1/26/2005 5:14:44 PM
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...

0
dlv (6)
1/26/2005 5:15:06 PM
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
> 
> 
> 
0
PeoSjoblom (790)
1/26/2005 5:17:07 PM
Reply:

Similar Artilces:

Hyperlinks to DB dont work now that DB is converted
We have converted all our Access 97 databases to Access 2000- not without a lot of issues. Most of them have now been researched and resolved, some re-coded, etc. We have one issue that we cannot resolve. We used to have hyperlinks on our Intra-net to several Access Databases. These were essentially "shortcuts" to the databases so that the general population could have easy access. Now that we did our Access conversion, the hyperlinks on our intranet do not work. Clicking on the link gives an option to open the file from its current location or download it to a local loc...

Send to Onenote on Windows 7, driver won't work?
I downloaded the driver from David Rasmussen, but every time I open OneNote after printing to XPS I get the message "Unhandled exception has occured in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Object reference not set to an instance of an object." If I click continue it still doesn't work. This was one of my favorite features - PLEASE PLEASE PLEASE find me a way to use OneNote! I lived off of it last year. Rilli wrote: > I downloaded the d...

Address list problems
I am receiving loads of these error messages. LDAP returned the error [32] Insufficient Rights when importing the transaction dn: CN=Recipient Update Service (Enterprise Configuration),CN=Recipient Update Services,CN=Address Lists Container,CN=holtswell,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=holtswell,DC=LOCAL changetype: Modify msExchReplicateNow:FALSE I have found this article http://support.microsoft.com/default.aspx?scid=kb;en-us;254030 I checked the write permissions on the exchange enterprise server group and can confirm I have 4 entries with write permission th...

auto caps don't work
in outlookk 2003, even tho i have set tools, options, spelling to autocorrect the first word of a sentence. it doesn't do it. i have never had this problem before, but now i have a new laptop and can't get this feature to work. -- tom martin Are you using Word as your editor? If so, your settings are there. Otherwise, learn to type. http://www.broderbund.com/jump.jsp?itemID=4713&itemType=CATEGORY&path=1%2C2%2C4713&ysmchn=GGL&ysmcpn=Typing&ysmcrn=sr2br29go633go202pi10ai50&ysmtrm=sr2br29go633go202pi10ai50+mavis+beacon&ysmtac=PPC&ovtac=PPC&SR=s...

step by step set up for outlook 2002- new address
Subject: From: "junne" <anonymous@discussions.microsoft.com> Sent: 10/16/2003 9:48:34 PM HELPPPPPPPPPPPPPP!!!!! i have been trying to set up my outlook so i can get hotmail and my other email at seattleschools.org on outlook but it won't work!!!! how/where can i get step-by-step intsructions???? for someone who is not very computer savvy... thanks a million! if the help files aren't enough help, buy a beginner's book. Many schools and ISPs have web pages with step by step instructions and pictures of every screen - if yours doesn't you can sear...

Search does not work for one workbook
I have workbook, but search (spotlight) often does not work for it. Drives me crazy. What can this be. I see peoples names in there. Kevs OS 10.6.2 INTEL imac Office 2004 Hi Kevs: It could be the file format the workbook is saved in. What extension does the file-name have? The extension tells the system what kind of data a file contains, which controls which importer Spotlight uses to read the file: if there is no extension you could get strange results. Cheers On 6/01/10 2:47 AM, in article C7689F21.5718F%forums55@verizon.net, "forums55@verizon.net"...

How does hibernate work
I have a question on the details of how hibernate works. I know it's saving everything in memory to the hiber file. Since power seems to be truly off at hibernation I assumed the flag to tell the PC to resume from the hiber file rather than normal booting must be either saved on disk or flashed to a bit of bios rom space, or maybe in space maintained by the clock battery. But the cure for a laptop that is stuck in a loop of constantly resuming from hibernating is to unplug it and take the battery out for a while so where actually is the flag to signal the system that it's in hi...

working with temporary working files..
I keep getting the message that I cannot save a temp. working file. This work has been save on a cd and transfered back on to my desk top. Help ...

Non-working work week
Is there any way to make an entire week a non-working work week? As in, a task is going to take 25 days, but the 4th of July occurs in the middle and you want to show that during that week (or 2), no work will occur. Besides creating a new task or extending the task during. Thanks in advance! Hi RVE, Open up the calendar, "Tools", 'Change working time..', then simply select the days you want as non working. Make sure that the calendar you change is the Project calendar and is the basis for all other calendars such as individual resource calendars. Hope thi...

GP8 Manufacturing
Hello Everybody. Our Shop works 5 days a week (closed weekends). We have a WC that works 6 days a week (down Sundays only). We have setup both the Shop and WC Calendars to reflect this reality. We would expect that, when scheduling an MO that routes thru this WC, the WC calendar will override the Shop Calendar? However, our tests are not showing this to be true - We use a Backwards Infinite Scheduling Method and it always skips the weekend when calculating the start day of an MO regardless of the WC. Does anyone have any experience working with the WC Calendars? Please share your wis...

Getting a cell address and using it
I have a spreadsheet containing production information. I want to b able to search by date (can do this using VLOOKUP), and extrac information from the same row as that date. However, I also want to be able to extract the previous 10 entries a well, for comparison purposes. ie I enter a date and find that it is in B95, and the piece of info want is in C95. I also want to be able to pull out C86-94 and place i all into a new sheet. I'd be grateful for any help offered. Thank -- dudar ----------------------------------------------------------------------- dudara's Profile: http://w...

Graphing work and remaining work over time
I would like to generate a graph showing total actual work and remaining work for a top level task over time (week), This would enable me to show the projected remaining work through the duration of the project as it decreases from N to 0, and show the actual work completed though the duration of the project as it increases from 0 to N. The canned visual reports don't allow me to do this (that I can see anyway). Maybe I'm missing something. I also can't see how to customize a visual report that will do what I want. Using Microsoft Project 2007 (not the Server edit...

POP Blank form on 8.0 and adding new address lines for country
I downloaded the new POP Blank Form from the website, due to some issues where you can't add any new calc fields to the report. I then modified the vAddressLine1, 2, 3 (remove the last "" and add Country, then created a new sAddressLine 4. The formulas are correct. They are setup identically to how our SOP ones are, how our old POP form was etc., so I know it's setup correctly. However, when I go to print POs, the data is inconsistent: New POs don't print the 4th line at all, older POs (ie POs that were in the system before upgrading to version 8) print with c...

outlook2003 send button does not work
send button does not work i have tryed a new email acc. and reinstalling with no luck. now i am useing express and then export to outlook ...

X400 Addresses from an LDAP Search
We are using Outlook 2003 connecting to a corporate LDAP server. I can add the LDAP server into Outlook, and it searches the server fine returning SMTP addresses. However, we need the X400 addresses instead. I have changed the following values: 6605001e=X400 6606001e=textEncodedORaddress But this did not work. However, if I set the following: 6605001e=X400 6606001e=mhsoraddress This does return X400 addresses in the address book - but only when selecting the address you want from the list of matches. When you have selected the correct address, it adds the display name to t...

Arabic fonts in Offive v.X:mac
Is anyone able to tell me if it's poosible to write from RIGHT to LEFT = with arabic fonts in Word v.X perhaps with an additional extension or = expansion kit? M. Timpe (It's ugly with all these TrueTypes, Font1, opentype) Hi � Go to Google, click on Groups, use Advanced search to search the NG archives for this group and for microsoft.public.mac.office.word, and you will get lots of info re workarounds. Suggest Arabic, Hebrew and "right to left" as separate search terms. Latest rendition mentioned a program called Mellel--hopefully people will share new info they have...

Drag and Drop does not work
Hi all. This is funny: When I try to drag and drop a message from "Inbox" to "Backup" I get an error message. When I right click and select "move to folder"- it does work. ANY CLUES? TIA Guy Not a single one. No Outlook version = no clues. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Guy Cohen asked: | Hi all. | | This is funny: | When I try to drag and drop a message from "Inbox" ...

Internet Explorer E-mail links are no longer working in Outlook?
Hi, As soon as I click an e-mail link in Internet Explorer, another Outlook pops up. It used to just open a fresh new e-mail screen with the persons address already in the "TO:" line ? I can't figure out what I did wrong. Thanks, Rich ...

SunTrust Auto-Download Not Working after Banking Upgrade
I have been automatically downloading transactions from SunTrust since I installed MS$ 2007 Deluxe last year. I have done this without having to signup (for a fee of 5.95/month) with SunTrust for MS$ support. AFter the MS Banking Upgrade, I had to re-establish my SunTrust account with MS$. But, now I'm told (by MS$) that I don't have a SunTrust on-line account. Any thoughts? Anyone? I have been having issue as well with Suntrust, even if you get it to work you will find that its basically useless now downloading and duplicating pending transactions repeatedly, this is where I am ...

Spell check not working.
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I reinstalled SL and all my MS office apps and updates. I noticed that spell check does not detect spelling errors. Auto spell check is on and even when I manual invoke spell check it does not detect errors. <br><br>I see a progress bar at the word that is intentionally misspelled and the only dictionary that I see and it is check is a custom dictionary. <br><br>Should I reinstall Word? <br><br>Thanks in advance. No, reinstalling is not a fix on a Mac in the overwhelming m...

Snapshot Viewer for Mac
Hey there, We send out reports using .snp files. We not have someone outside who has a Mac at home. I can't find snapshot viewer for Mac, I am assuming one is not made. Can anyone verify this or tell me what program can open a .snp file on a Mac? Thanks, Nate In article <1d9401c3e05e$b02bbd30$a001280a@phx.gbl>, "Nate" <anonymous@discussions.microsoft.com> wrote: > Hey there, > > We send out reports using .snp files. We not have > someone outside who has a Mac at home. I can't find > snapshot viewer for Mac, I am assuming one is not ...

Number of outgoing addresses
Anyone know if there is a limit to the amount of addresses you can use for a letter? I find that other letters get sent except for one that just wll not go and it has around 50 e-mail addresses. The letter itself has no attachments or images just a short note. Your ISP sets the limit on recipients. Contact them. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Red Stripe" <RedStripe@discussions.microsoft.com> wrote in message news:23973094-A025-4ADD-B49A-FF63DA604389@microsoft.com... > Anyone know if there is a limi...

Working with option buttons in Word 2002
So, we are trying to create a form in Word 2002 in which we offer a series of radio or "option" buttons for some of our survey questions. However, we are struggling to make the option buttons work through the "Web Tools" toolbar. We can create the list we want to create, but can't save the file so it can be e-mailed and utilized in the correct capacity. Can someone please walk me through the steps of how to create a form in which there are numerous questions, almost all of which utilize the same clickable "option buttons" to choose either a &q...

Entourage not storing recent addresses
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hi, I've recently noticed that Entourage is not storing new email addresses in it's 'recent addresses' memory. I have sent an e-mail to the same address about 4 or 5 times and each time I have to enter the whole address - previously Entourage would remember the e-mail address and I would just have to enter the first few letters. Does anyone know why this is happening? Could my recent addresses memory be full? Thanks Jack "hum@officeformac.com" wrote: > Does anyone know why this is happ...

Working with WORKS
We have some spreadsheets in WORKS 4.5 fpr Windows 95. But Excel 2000 won't open them. It says that the version is incompatible. How can that be if it is an older version of WORKS? Using Windows XP. -- ____________________ Bill DeShawn Please reply to group only Thank you http://my.sterling.net/~bdeshawn Generally you would normally open the file in WORKS and then save it down as a file that Excel can read. .xlr is a native Excel format, or .txt, .csv, dbaseIV are all formats it can read. You can also find a converter here if you don't have WORKS. http://www.rl-software.co...