Question

Locked

Add decimal to MAC Address in Excel

By kminatta ·
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.

thanks.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

There might be an easier way.....

by ThumbsUp2 In reply to Add decimal to MAC Addres ...

You said MAC addresses and the one's I've seen have dashes. Do you mean IP addresses with decimals? No matter. 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, ".", B1, ".", 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.

---

Collapse -

Thanks

by kminatta In reply to There might be an easier ...

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." "8901". 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. 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?

Collapse -

Try turning off number checking......

by ThumbsUp2 In reply to Thanks

Tools -> Options -> Error Checking

Uncheck the box for "Numbers stored as text".

This will prevent Excel from changing any number with a decimal inside of it TO a number instead of text. To check it, if the contents of a cell automatically aligns itself to the right, it's stored as a number and will "round" what follows the decimal. If it automatically aligns itself to the left and doesn't "round", it's stored as text and "shouldn't" put an apostrophe before the contents using your method.

Collapse -

The easy fix at last

by kminatta In reply to Try turning off number ch ...

i posted the same question to another board, and i received the following answer:

=Mid(a1,1,4)&"."&Mid(A1,5,4)&"."&Mid(A1,9,4)

works flawlessly in one easy step! thanks for the help.

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums