Hi,

I don't know much about macros in excel but have used a few from the
postings from this group I am trying to create FIPS code for
counties, the way I have the data, the state code is given by
1,2,3....50, the county codes are 1, 2,3....15...100. To make a fips
code the state code 2 digits (i.e. if the code is 1 it has to be made
02) and the county code in 3 digits i.e. if its 1 it has to be made
001, are joined, so a county with state code 1 and county code 1 will
have a fips code of 01001, with county code 22 will have a fips code
of 01022 and so on. The way I am trying to do is I am recording a
macro by first converting the state column to 00 format and then
converting the county column to 000 format, inserting a new column-
FIPS, formating the fips column to 00000 and then concatenating the
state and county column, using CONCATENATE function in the new column,
the problem is that during concatenation excel removes the trailing 0,
ie if I concatenate 01 and 001, the result is 11 though I want it to
be 01001 ie 5 digits, it does not work even if I format the fips
column to 00000. Can anyone please advise how to do this using a macro
or some other script so that after concatenation the fips code will be
5 digits?

thanks

Singh