Home > Data Services > Catalog . Restricted Data . Census . ACS

Search Data Services

Meta Search
search across all the following databases:

Data Catalog
Data and documentation

KnowledgeBase
Common questions and answers.

Resources
Entire collection of data resources.


Latest Data News

RSS Feed icon

Sage Stats and CQ Political Stats Trial

Breaking News: Protect the 2020 Census

The Fastest Growing US Cities Are Mostly In the West

Another rule from NIH: Demographers might get to ignore this one

Research on Health Disparities: Incarceration Matters

Data Services Knowledge Base

Q:  

I have some census characteristics that I want to put into GIS. However, I am having trouble doing so because the FIPS codes in my data are represented as numbers; e.g., 1 for Alabama instead of 01. Likewise, Autauga county is represented as 1 instead of 001. I need an ID variable for Autauga county, Alabama that looks like 01001.

How can I convert numeric data back to text in Excel? Right now, state is in one column and county is in the next column.

A:  

Assume that your state code is in column A and your county code is in column B.

Column C will be the text version of column A. The following is the formula that should be used for cell C2:

=CONCATENATE(IF(A2<10,CONCATENATE("0",A2),IF(A2<100,CONCATENATE(A2),A2)))

Column D will be the text version of column B. The following is the formula that should be used for cell D2:

=CONCATENATE(IF(B2<10,CONCATENATE("00",B2),IF(B2<100,CONCATENATE("0",B2),B2)))

Column E will be the combination of columns C and D: =CONCATENATE(C2,D2)

The example is presented in the spreadsheet below. If you do not understand these formulas or how to apply them, you can open up the spreadsheet and paste your values for state and county in columns A and B and the modifications will show up in columns C – E.

http://www.psc.isr.umich.edu/dis/data/kb/downloads/t1074_concatenate.xls

Downloads:

Related Question Groups: