Ok i have close to 10000 records and they look like bg1234 and i need
to add 00000 after the bg but i also have to make the bg capital
letters. How do i set up a marco to just look for the bg because i
have other records that begin with other letters. Another problem is
that i might have some records with bg123 but the i need to add 000000
after the bg . Any ideas?? Thanks a lot

Hi,

=UPPER(LEFT(A1,2))&IF(LEN(A1)=6,"0000","00000")&MI D(A1,3,LEN(A1))

should do the job ...

HTH
Cheers
Carim

ok it works but i forgot to say there is other data that have an h
insted of bg so i have to write a vba function to search and update
each cell from top to bottom
Carim wrote:
Hi,

=UPPER(LEFT(A1,2))&IF(LEN(A1)=6,"0000","00000")&MI D(A1,3,LEN(A1))

should do the job ...

HTH
Cheers
Carim

ok it works but i forgot to say there is other data that have an h
insted of bg so i have to write a vba function to search and update
each cell from top to bottom
Carim wrote:
Hi,

=UPPER(LEFT(A1,2))&IF(LEN(A1)=6,"0000","00000")&MI D(A1,3,LEN(A1))

should do the job ...

HTH
Cheers
Carim