Concatenate Problem
 

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

Concatenate Problem
 

Hi,

Not a macro but you can use a formula like this:

=TEXT(A1,"00")&TEXT(B1,"000")

HTH
Jean-Guy

"singh" wrote:

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

Concatenate Problem
 

On Feb 9, 1:01 pm, pinmaster
wrote:
Hi,

Not a macro but you can use a formula like this:

=TEXT(A1,"00")&TEXT(B1,"000")

HTH
Jean-Guy

"singh" wrote:
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

Thanks a lot, this works, but is there any way I can apply this to the
whole column without having to drag the formula down till the last
row..I have more than 3000 rows in over 150 tables, thats the reason
I was thinking this may be possible with macro or a script..

Singh

Concatenate Problem
 

If you have data in the adjacent cells then you can double click the drag
icon. It will copy until the first empty adjacent cell.

HTH
Jean-Guy

"singh" wrote:

On Feb 9, 1:01 pm, pinmaster
wrote:
Hi,

Not a macro but you can use a formula like this:

=TEXT(A1,"00")&TEXT(B1,"000")

HTH
Jean-Guy

"singh" wrote:
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

Thanks a lot, this works, but is there any way I can apply this to the
whole column without having to drag the formula down till the last
row..I have more than 3000 rows in over 150 tables, thats the reason
I was thinking this may be possible with macro or a script..

Singh