Cleaning Up Data

C

craig.buchinski

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
 
C

Carim

Hi,

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

should do the job ...

HTH
Cheers
Carim
 
C

craig.buchinski

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
 
C

craig.buchinski

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
 
Top