Convert US states to "USA"

[Ramaskriget]

Hello there,

I have a dataset with tweets from all over the world. I would like to classify those people who gave a US state as their location, as living in the USA. So in short, all the rows that contains a 2 digit state code should be converted to "USA". I could do this manually, but I will need the function in the future as well, so that's a lot of work. 

How can I do this?

Thanks in advance. 

[Zubair_Muhammad]

HI @Ramaskriget

Try this. First create a separate Table with all the States in USA. Lets call it US_States

From the Home Tab>>New Data....Copy paste the data below

Name	Code
Alabama	AL
Alaska	AK
Arizona	AZ
Arkansas	AR
California	CA
Colorado	CO
Connecticut	CT
Delaware	DE
Florida	FL
Georgia	GA
Hawaii	HI
Idaho	ID
Illinois	IL
Indiana	IN
Iowa	IA
Kansas	KS
Kentucky[E]	KY
Louisiana	LA
Maine	ME
Maryland	MD
Massachusetts[E]	MA
Michigan	MI
Minnesota	MN
Mississippi	MS
Missouri	MO
Montana	MT
Nebraska	NE
Nevada	NV
New Hampshire	NH
New Jersey	NJ
New Mexico	NM
New York	NY
North Carolina	NC
North Dakota	ND
Ohio	OH
Oklahoma	OK
Oregon	OR
Pennsylvania[E]	PA
Rhode Island[F]	RI
South Carolina	SC
South Dakota	SD
Tennessee	TN
Texas	TX
Utah	UT
Vermont	VT
Virginia[E]	VA
Washington	WA
West Virginia	WV
Wisconsin	WI
Wyoming	WY

Then you can add a calculated column in your Main Table to check if the State is a USA state or not

Column =
IF ( CONTAINS ( US_States, US_States[Code], TableName[Code] ), "USA", "Others" )

[Zubair_Muhammad · Accepted solution]

@Ramaskriget

Please see attached file