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

A questionnaire change: politics or not?

Finding “war brides” in the ACS

Census Bureau is considering adding a new Middle East/North Africa ethnic category

Big Data Initiative at NIH-OBSSR

UM Now Has Access to IndiaStat

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: