custom format

**gazd** · 09-03-2008, 09:47 PM

i want to format a cell to display a UK national insurance number in the following format, I enter WG256759C IN CELL A:1 and want it to display like this: WG 25 67 59 C

**Paul** · 09-03-2008, 09:55 PM

Hi Gazd, welcome to the forum.

Please take a moment to read the forum rules, paying close attention to #5 (posting in the correct sub-forum). I've moved your post this time, but future posts not adhering to the forum rules may be locked or deleted.

Thanks!

**mdbct** · 09-03-2008, 10:47 PM

Unlike numbers, text cannot be reformatted in the manner you wish.

You can, however use code to change the formatting.
Right click on the tab you want to formatting to change and then choose View Code.
Paste the following - change the C:C in the Intersect(Range("C:C"),Target) section to the column you want to change.

Please Login or Register  to view this content.

**mohd_w50** · 09-04-2008, 03:37 AM

Mdbct,

what do you mean by "right click on the tap" ? what tap ?

I tired to creat a command bottom and put the code in it but it didn't work.

I appreciate your help

**oldchippy** · 09-04-2008, 03:50 AM

Originally Posted by gazd

i want to format a cell to display a UK national insurance number in the following format, I enter WG256759C IN CELL A:1 and want it to display like this: WG 25 67 59 C

Try this in B1 copied down

=LEFT(A1,2)&" "&MID(A1,3,2)&" "&MID(A1,5,2)&" "&MID(A1,7,2)&" "&RIGHT(A1,1)