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

Tools: Data as Text

More fun with names

Counting Same-Sex Couples

The Antidote for “Anecdata”: A Little Science Can Separate Data Privacy Facts from Folklore

Big Data: NYC Taxi Cab Trips

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: