Excel, how do I make cell text fit a pattern? 1

[Grogo]

Excel 2002 SP3

I am entering 12 character alpha-numeric data into a cell. I need to the data into 3 groups of 4 each seperated by a period. Is it possible to set a custom cell format so that when I paste the date into the cell it is formatted?

Example:
I have a MAC address with no hyphens or colons
00a0ff02000a

I paste it into the cell & I need it displayed as
00a0.ff02.000a

Any help would be greatly appreciated.

Thanks!

Grogo

 

[Lilliabeth]

In the Format Cells dialog box, go to the Numbers tab and choose Custom. In the Type box, remove whatever is in there and replace it with the following:
0000"."0000"."0000

 

[gizmo1973]

=LEFT(A5,4)&"."&MID(A5,5,4)&"."&RIGHT(A5,4)

If your data is in a5 (if not just change) and is always 12 characters long this should work

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[Grogo]

Hmmm, I tried that but nothing changed at all.

 

[gizmo1973]

Lilliabeth, I think the format will only work with numeric not alphas and numeric as Grogo wants

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[gizmo1973]

Which one mine or Lilliabeth, forgot to say mine should be typed in the cell directly next to the cell containg your data not as a format, sorry!!!!

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[Lilliabeth]

My bad. Alpha chars not welcome. You will have to use a formula

 

[Grogo]

Gizmo, I am pasting the data into the cell from a .txt file so there won't be a cell to reference. Is there any way to do this using Format Cells or Conditional Formatting?

Grogo

 

[gizmo1973]

Bt if you are pasting from a text file you must be pasting to[b/] somewhere and as you said Excel 2002 I assume that's where it is going?
Once the data is in your Excel file then put the formula next to the data and voilla your format will be ****.****.**** in the new cell.
Formating text and numeric this was is tricky (but do-able I'd say but not by me I'm afraid)

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[Grogo]

OK, I got it to work like that. I'll just hide the extra column. I was hoping for something like an input mask.

Thanks for your help Gizmo & Liliabeth!

Grogo

 

[gizmo1973]

ah input mask young jedi Acess user!!!

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts