• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4672
  • Last Modified:

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:


... 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!
1 Solution
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:

To split by every 2 characters, i just used a few MID functions

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

IrrylynAuthor Commented:
Thank you Matt.  That works perfectly!

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


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


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'

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now