Excel Formula or Macro to Format MAC Addresses

Good morning experts!

Users send in tickets that require a MAC address.  They come in all different kinds of formats:

XX:XX:XX:XX:XX:XX
XX.XX.XX.XX.XX.XX
XXXX.XX.XX.XXXX
XXXX.XXXXXX.XX
XX-XX-XX-XX-XX-XX
XXXXXXXXXXXX

... you get the idea...

So we copy and paste the info from the ticket into a spreadsheet for processing and then work of the spreadsheet to do them in bulk.
 
In one application, we have to enter the mac in all lower case letters with no special characters.  (xxxxxxxxxxxx)

Then in another application, we have to enter the MAC in all upper case letters with : as separators.  (XX:XX:XX:XX:XX:XX)

So I would like to paste into column A however it is in the ticket and then have column B automatically remove any special characters and make all lower case.  Then Column C would automatically make them upper case and add the : where needed.

Does anyone know a formula to  make this happen?

I am using Excel 2007 (MS Office Professional)

Thank you!
IrrylynAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
Hi Irrylyn,

To make it all lower case letter, you can use this combination of LOWER() and SUBSTITUTE(). I only wrote it to remove : . , but if you have more characters to remove just add more substitute shells:
=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,":",""),".",""),"-",""))

To split by every 2 characters, i just used a few MID functions
=UPPER(MID(B1,1,2)&":"&MID(B1,3,2)&":"&MID(B1,5,2)&":"&MID(B1,7,2)&":"&MID(B1,9,2)&":"&MID(B1,11,2))

Change B1 to whatever your first formula is to make lower case.

Matt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IrrylynAuthor Commented:
Thank you Matt.  That works perfectly!

I just wanted to add a substitute for space or comma also so ended up with:

=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,":",""),".",""),"-","")," ",""),",",""))

and then for my C column, just how you had it:

=UPPER(MID(B2,1,2)&":"&MID(B2,3,2)&":"&MID(B2,5,2)&":"&MID(B2,7,2)&":"&MID(B2,9,2)&":"&MID(B2,11,2))

Makes perfect sense now that I can read it.  I didnt realize you could nest those functions.

Note for others who might find this useful:  Make sure you format the columns to 'General'

=)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.