I have a list of MAC addresses in a spreadsheet that currently do not contain any decimals. I would like to painlessly add the decimals in the correct position. The only way that i have been able to figure out so far is to use text to column to split the MAC address into three cells, and then use an add-in called ASAP Utilities to insert a decimal at the start and end of the second cell (from what i can tell, ASAP doesn't allow you to pick a location in the cell other than start and end). Then i have to use a formula to combine all three parts back together. I know that there has to be an easier way, but i haven't been able to find it yet. You said MAC addresses and the one's I've seen have dashes.

Oct 19, 2005  I'm trying to create an Excel sheet to display MAC addresses in the following format - 00:00:00:00:00:00 However. The characters can be either letters or numbers.

Do you mean IP addresses with decimals? The procedure is the same. First, split the address into as many columns as needed using text to column feature like you did before. Now, using the empty column to the right or left of them (insert a column if needed), use the CONCATENATE function to put them all back together inserting a decimal where needed. It goes like this: (1) Your numbers with spaces are all in column A to start with. (2) Use text to columns to split those numbers to as many columns as you need. (3) Use the empty column to the right or left (insert one if needed) of where all the resulting columns of data reside.

(4) Now, assuming you're on row 1 column D, the formula would look something like this (modify as necessary): =concatenate(A1, '.' , C1) (5) Copy that formula down each row to the bottom of the data. (6) Once you've varified that everything is correctly copied and formatted (the correct numbers are represented), select all of the cells in that column. Do not click the column header to select. Be sure just the filled cells are selected and COPY them (ctrl+c or right click and choose copy).

(7) Place the cursor back at the top of that column, use Edit/Paste Special, choose Values from the resulting menu, then click OK. This will replace the formula which is only displaying the numbers with the actual numbers themselves formatted as text and not numerical. The contents of every cell will begin with an appostrophe though. So be aware of that if you need to use that data in other applications. ( Delete the old split out columns as you no longer need them. Hope this helps. Thanks for the help.

This is how I am currently doing it, but it takes several steps and I was hoping to do it in a more efficient manner. What I do after splitting the values with 'text to columns', is use ASAP utilities to insert the '.' Before and after the middle two octets (i.e., '.4567.' I now have three columns, '0123' '.4567.' I then use this formula (=a1&b1&c1) to merge the values into one cell. ASAP utilities has a function that converts the formulas to their values. 'outlook for mac default \'show details\''. ASAP will do it without inserting the apostrophe.

I have even tried using custom formatting, but it doesn't change the format. Any other suggestions?

For security reasons we need to retrieve the mac address of system. MAC addresses are 12-digit hexadecimal numbers written in format MM:MM:MM:SS:SS:SS We can retrieve the mac address using various ways, but I am going to explain how do we get the mac address using excel vba. Its not a very difficult task to get mac address using excel macro. Please copy and paste below code into your excel macro and it will automatically show you the mac address of your system.

Function GetMACAddress() As String Dim sComputer As String Dim oWMIService As Object Dim cItems As Object Dim oItem As Object Dim myMacAddress As String sComputer = '.' Set oWMIService = GetObject('winmgmts: ' & sComputer & ' root cimv2') Set cItems = oWMIService.ExecQuery('SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True') For Each oItem In cItems If Not IsNull(oItem.IPAddress) Then myMacAddress = oItem.macAddress Exit For Next 'it will return mac address in format MM:MM:MM:SS:SS:SS MsgBox myMacAddress GetMACAddress = myMacAddress End Function It will show you mac address into alert as well return string format when you call this function. Hope it will help you guys.