2 Questions about Excel

[LewisReeder]

These questions pertain to a spreadsheet with thousands of ID numbers....

1. How do you check to make sure a number is 6 characters long and add zeros to the beginning if it is not?

2. How do you check to make sure there is not a space before a number and remove it if there is?


Thanks,
Lewis

 

[longhair]

LewisReeder,
loop through the rows
1) use the len() function
2) use the ltrim() function
hth
regards,
longhair

 

[xlbo]

kinda smae lines as longhair but this can be done in 1 formula, next to your cirrent data

=TEXT(TRIM(A2),"000000")

where your 1st ID number is in A2

copy down to end et voila

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[LewisReeder]

Geoff,
The formula works perfectly, however I will be moving these numbers into tables for a database. How can I remove the reference and make them real numbers?

 

[longhair]

LewisReeder,
select entire range
copy
paste special
choose 'values'
hth
regards,
longhair

 

[LewisReeder]

Thanks guys.... you're both a big help.