NINO Data Validation

[Im Sarah]

Hi All

I'd really appreciate some help with formatting cells with UK National Insurance Numbers. These consist of letters and numbers with spaces in between such as seen below where T represents any letter and 0 represents any 0 - 9 number

TT 00 00 00 T

The way I was doing it was just to emit the spaces and validate the cell to 9 characters however I am now told I MUST format the NINO with the spaces in. I've done some research on these message boards but haven't found an answer as yet. I'm still at novice level so have no clue about code and macros unfortunately.

If I didn't need the spaces as shown above I think this would have worked but I don't know how to tell it to put the spaces in.

=AND(LEN(A1)=9,ISERROR(MID(A1,1,1)*1),ISERROR(MID(A1,2,1)*1),ISNUMBER(MID(A1,3,6)*1),ISERROR(MID(A1,9,1)*1))

Please help!

Thanks

 

[oldbrewer]

TT 00 00 00 T

ok

TT 00 00 0 0T

error5

T 00 00 00 TT

error2

TT000000T

error1

TT000000 T

error2

TT 00 000 0 T

error4

TT 000 0 00 T

error3

TT 00 00 00 T

ok

formula in D1

=IF(LEN(A1)<>13,"error1",IF(MID(A1,3,1)<>" ","error2",IF(MID(A1,6,1)<>" ","error3",IF(MID(A1,9,1)<>" ","error4",IF(MID(A1,12,1)<>" ","error5",IF(ISERROR(MID(A1,4,2)*1*MID(A1,7,2)*1*MID(A1,10,2)*1),"error6","ok"))))))

still need to check letters are letters and numbers are numbers

A

65

aand ensure spaces are where they should be

Z

90

1

2

3

4

5

6

7

8

9

10

11

12

13

OUTCOME

a

97

mytable

TT 00 00 00 T

y

y

y

y

y

y

y

y

y

y

y

y

y

ok

row 21

z

122

T0 00 00 0 0T

y

oops

y

y

y

y

y

y

y

y

oops

oops

y

error

-444

oops

T 00 00 00 TT

y

oops

oops

y

oops

oops

y

oops

oops

y

oops

oops

y

error

1

oops

TT 00 00 00TT

y

y

y

y

y

y

y

y

y

y

y

oops

y

error

65

y

TT000000 T

y

y

oops

y

y

oops

y

y

y

oops

oops

y

y

error

91

oops

TT 00 000 0 T

y

y

y

y

y

y

y

y

oops

oops

y

y

y

error

97

y

TT 000 0 00 T

y

y

y

y

y

oops

oops

y

y

y

y

y

y

error

123

oops

TT 00 00 00 T

y

y

y

y

y

y

y

y

y

y

y

y

y

ok

444

oops

B21

=VLOOKUP(CODE(MID($A21,B$20,1)),mytable,2)

copied to C col, and N column

mytable2

-444

oops

48

y

D21

58

oops

=IF(CODE(MID($A21,D$20,1))<>32,"oops","y")

444

oops

copied to G col, J col, M col

E21

=VLOOKUP(CODE(MID($A21,E$20,1)),mytable2,2)

copied to F,H,I,K,L

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="4"><col><col span="13"></colgroup><tbody>
</tbody>

 

[Im Sarah]

Many thanks for your reply, I really am quite basic on excel (although I am trying my best) and don't know how to implement what you have written. I tried the formula you had written below d1 and put it as a custom data validation but it didn't work.

Basically I have a thousand or so National Insurance numbers that have been recorded as TT000000T and I need to format the cells to put the gap in so they all read TT 00 00 00 T

Please help

 

[oldbrewer]

so the NEW question is different lol

use find and replace

find TT0 replace with TT 0

gives TT 000000T

find 000000 replace with 00 00 00 (and a space after the last 0 that You have to accept is there)

JOB DONE

IT WILL IGNORE ALL OTHER VARIANTS LIKE TT 00 00 00 T OR TT 00 0000 T

 

[oldbrewer]

forget my last post

assuming the NI numbers are in column A

for j=1 to 10000
if len(cells(j,1))=9 then cells(j,1)=left(cells(j,1),2)&" "&mid(cells(j,1),3,2)&" "&mid(cells(j,1),5,2)&mid(cells(j,1),3,2)&mid(cells(j,1),7,2)&right(cells(j,1),1)
next j