Formula help

[yasin]

Hello,

I am trying to find a formula to find same first three digits of zibCode in a big data column. return value to be "1" or "0".

example:

zibcode

59527

59528

59523

59505

80120

80133

80122

Thank you for your help.

[nerces]

You can use left([zibcode], 3) to extract the first 3 digits and use that to compare. I can help you further with a workflow if you provide further detail and an example of what you expect the output to look like.

[yasin]

Hi, 

I have attached a sample of my data sheet.

ZipCode	State	City	Population	SquareMiles
59001	Montana	Absarokee	1,566	135.7
59002	Montana	Acton	63	15.3
59003	Montana	Ashland	1,023	316.7
59006	Montana	Ballantine	865	67.8
59007	Montana	Bearcreek	120	59.1
59008	Montana	Belfry	374	85.0
59010	Montana	Bighorn	185	513.9
59011	Montana	Big Timber	3,014	1050.2
59012	Montana	Birney	170	236.1
59013	Montana	Boyd	203	30.8
59014	Montana	Bridger	1,474	842.3
59015	Montana	Broadview	488	338.9
59016	Montana	Busby	936	497.9
59018	Montana	Clyde Park	408	104.9
59019	Montana	Columbus	3,347	290.5
59020	Montana	Cooke City	112	103.7
59022	Montana	Crow Agency	2,290	591.7
59024	Montana	Custer	348	511.8
59025	Montana	Decker	96	533.3
59026	Montana	Edgar	193	47.0
59027	Montana	Emigrant	372	127.8
59028	Montana	Fishtail	518	340.8
59029	Montana	Fromberg	770	33.7
59030	Montana	Gardiner	1,792	1086.1
59031	Montana	Garryowen	296	40.7
59032	Montana	Grass Range	391	387.1
59033	Montana	Greycliff	121	29.9
59034	Montana	Hardin	4,726	847.0
59035	Montana	Yellowtail	326	479.4
59036	Montana	Harlowton	1,630	711.8
59037	Montana	Huntley	1,554	193.5
59038	Montana	Hysham	720	774.2
59039	Montana	Ingomar	121	864.3
59041	Montana	Joliet	1,748	154.1
59043	Montana	Lame Deer	2,908	370.4
59044	Montana	Laurel	9,618	186.0
59046	Montana	Lavina	408	600.0
59047	Montana	Livingston	12,103	854.1
59050	Montana	Lodge Grass	1,731	562.0
59052	Montana	Mc Leod	199	537.8
59053	Montana	Martinsdale	378	771.4
59054	Montana	Melstone	282	258.7
59055	Montana	Melville	142	211.9
59057	Montana	Molt	442	300.7
59058	Montana	Mosby	40	307.7
59059	Montana	Musselshell	164	213.0
59061	Montana	Nye	305	160.5
59062	Montana	Otter	79	395.0
59063	Montana	Park City	1,704	98.4
59064	Montana	Pompeys Pillar	215	179.2
59065	Montana	Pray	88	16.0
59066	Montana	Pryor	713	373.3
59067	Montana	Rapelje	221	345.3
59068	Montana	Red Lodge	3,498	622.4
59069	Montana	Reed Point	463	208.6
59070	Montana	Roberts	758	91.4
59071	Montana	Roscoe	106	67.1
59072	Montana	Roundup	4,032	1376.1
59074	Montana	Ryegate	604	511.9
59075	Montana	Saint Xavier	255	303.6
59076	Montana	Sanders	47	11.3
59077	Montana	Sand Springs	83	592.9
59078	Montana	Shawmut	148	255.2
59079	Montana	Shepherd	3,059	242.6
59081	Montana	Silver Gate	26	2.1
59085	Montana	Two Dot	76	304.0
59086	Montana	Wilsall	757	411.4
59087	Montana	Winnett	493	1700.0
59088	Montana	Worden	1,203	333.2
59089	Montana	Wyola	491	306.9
59101	Montana	Billings	36,335	498.8
59102	Montana	Billings	44,391	15.1
59105	Montana	Billings	23,343	103.5
59106	Montana	Billings	8,009	126.9
59201	Montana	Wolf Point	5,027	1416.1
59211	Montana	Antelope	174	122.5
59212	Montana	Bainville	310	233.1
59213	Montana	Brockton	855	527.8

Requirement: 

 For this case, rural is defined as an area with less than 100,000 people. To simplify this analysis, areas are defined as having the same first three digits of the ZIP code.

• Add a “rural” variable to the ZipCodes tab indicating whether each ZIP code is associated with a rural city (1) or an urban city (0).

[nerces · Accepted solution]

Thank you for the extra detail. I've attached a solution for you. Please let me know if you have any issues.

[yasin]

Hi,

After I run the hole data set j join given me all null. could you please look into it. 

Thanks