Spaces and numbers

R

rexmann

Hi

I have a 2 problems in excel. I have a column with an emplyees NI number but the data entry people put gaps between the numbers NY 78 78 65 D. I need to remove the gaps. However there are 1000s of numbers and I wanted to know if there is a way of doing the lot?

Also some people have entered the wrong code (should be 9 digits eg. NY238722D). Is there a facility to hightlight any figures that are not 9 digits. Even better a facility to check the format (eg. LL 00 00 00 L) of letters and numbers.

Any help would be greatly appreciated.

Kind regards

Rexmann
 
A

Andy B

Hi

You could just use Find/Replace to get rid of the spaces.
Once done, alongside the data use a formula like:
=LEN(A2) to return the length (hopefully 9!) . . or
=LEN(A2)=9 to return TRUE or FALSE

--
Andy.


rexmann said:
Hi

I have a 2 problems in excel. I have a column with an emplyees NI number
Click to expand...
but the data entry people put gaps between the numbers NY 78 78 65 D. I need
to remove the gaps. However there are 1000s of numbers and I wanted to know
if there is a way of doing the lot?
Also some people have entered the wrong code (should be 9 digits eg.
Click to expand...
NY238722D). Is there a facility to hightlight any figures that are not 9
digits. Even better a facility to check the format (eg. LL 00 00 00 L) of
letters and numbers.
 
H

Harry Bo

rexmann,

To remove the spaces(gaps), highlight the column(s) hit edit - replace - find what (hit the space bar) - replace with (leave blank).

For highlighting the data not equal to nine digits, use conditional formatting that maybe colours the cells red if the do not equal 9 .
=IF(LEN(A1)=9,"", "error") ( error will equal your condition you choose)

modify to your requirements

Harry
 
R

rexmann

To Harry Bo and Andy B - thanks for the answers - both worked a treat.

cheers Rexmann
 
A

Andy B

Thanks for the thanks!

--
Andy.


rexmann said:
To Harry Bo and Andy B - thanks for the answers - both worked a treat.

cheers Rexmann
Click to expand...
but the data entry people put gaps between the numbers NY 78 78 65 D. I need
to remove the gaps. However there are 1000s of numbers and I wanted to know
if there is a way of doing the lot?NY238722D). Is there a facility to hightlight any figures that are not 9
digits. Even better a facility to check the format (eg. LL 00 00 00 L) of
letters and numbers.
 
Top