Formatting an Excel cell to add dot separators for mac address 1

[jimdevon]

I am trying to take a cell with a text format and add dots between each set of 2 characters for showing a NIC mac address. Thanks in advance.

 

[kjv1611]

Is there already a separate there, or are they strung all together? The separate could be a space, dash, whatever, just is there any way to tell other than counting them?

 

[jimdevon]

No, just letter and number combinations all together.

 

[jimdevon]

I could, of course, use left * starting at, etc. but I would think you could just create a cell format with the . and paste the cells, but apparently not, at least as far as I can tell.

 

[TonyJollans]

You can't do this with a number format - it is not a number. About the only text formatting you can do - AFAIK - is to use @ to represent the whole text string.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[kjv1611]

Okay... so let me get this straight - on original idea...

I can't remember..

Is it always exactly 2 characters per piece, and if so, how many pieces are we talkign? is it 6, like it looks like at link below?

http://en.wikipedia.org/wiki/MAC_address

So you could do this with an embedded formula...

you'll need:
--CONCATENATE
--LEFT
--MID
--LEN ... maybe, but maybe not, since it's a fixed length...
--RIGHT

I think that's it... I'll see what I can put together in just a sec...

 

[kjv1611]

Here you go. Of course, change the cell reference to wherever you have the data:

=CONCATENATE(LEFT(C10,2),".",MID(C10,3,2),".",MID(C10,5,2),".",MID(C10,7,2),".",MID(C10,9,2),".",RIGHT(C10,2))

 

[kjv1611]

And since Excel can't make any sense of numbers and letters, nor a series of numbers with decimals all throughout it, it shouldn't matter what format your destination cell is in, I don't think - or at least it shouldn't matter whether it's General or Text.

 

[kjv1611]

If you want to do a VBA route on it, then of course, you'd do about the same thing, but you could put it in a loop, and then refer your procedure with a button to just change them all in place.

Just depends upon what you need/want to do, I guess.

In my test with the formula, I got this:

D1DA5A15D1A5 D1.DA.5A.15.D1.A5
A5D1F35AS1D3 A5.D1.F3.5A.S1.D3
5S5A1SD5F15A 5S.5A.1S.D5.F1.5A

 

[jimdevon]

That is it! What the deal is I just rolled out about 50 new laptops. I need to create a sharepoint document with the IP, mac address, etc. The mac address field should normally look something like 23.rt.45.3g.45.gj (not a valid address, but the format is right), what I got is more like 23rt453g45gj, anyway. I need to try to follow your formula, because the outcome looks right.

thanks,
Jim

 

[jimdevon]

Thanks kjv1611. I have another question, just curiosity. If I concatenate a cell, can the contents be made permanent. In other words, can the concatenated data be fixed so it is no longer dependend on the parent cells?

Thanks for the help on this one. It worked beautifully,

 

[mintjulep]

You can copy then Paste Special | Values to make them "permanent".

 

[kjv1611]

Oh yeah - what mintjulep said. I use that one all the time!

 

[jimdevon]

Thanks guys.