As shown in the figure, column A is the original data, columns B and C are the results of function calculation, and columns E and F are the corresponding tables of provinces and cities.
The functions of columns b and c are:
The function of column B is a variant of C. Let's explain column C first.
The meaning of column C is to find out whether cell A2 contains the contents of cell F2-F620. If so, the contents of cell F2-F620 will be displayed; if not, the contents of cell F2-F620 will be displayed. That is, whether there is a city name in A2 is searched, and if there is, the name of the city is displayed.
Column b indicates that if C2 is NA, check whether E2-E620 is included in A2; If C2 is not North America, the pane containing the corresponding contents of F2-F620 and E2-E620 is searched in C2. That is, if the city name is not found in the address of C2, it is found whether there is a province name; If the city name has been found in C2, then find the city in column F and take its provincial name.
This effect can simply extract the names of provinces and cities from the address, and others that cannot be calculated need to be handled manually. You can modify the variation according to your own needs.
Hefei, Anhui
Suzhou, Anhui
Huaibei Anhui
Fuyang, Anhui
Bengbu city, Anhui province
Huainan Anhui
Anhui Chuzhou
Maanshan, Anhui
Wuhu City, Anhui Province
Tongling Anhui province
Anhui Anqing
Huangshan, Anhui
Anhui Lu 'an
Anhui Chizhou
Anhui Xuancheng
Anhui Bozhou
Chaohu, Anhui
Xiamen City, Fujian Province
Fuzhou, Fujian
Fujian Nanping
Fujian Sanming
Fujian Putian
Quanzhou Fujian
Fujian Zhangzhou
Fujian Longyan
Fujian Ningde
Fujian Changle
Fujian Shishi
Fujian jinjiang
Gansu Lanzhou
Jiayuguan in Gansu province
Jinchang, Gansu Province
Gansu Baiyin
Gansu Tianshui
Gansu Jiuquan
Gansu Zhangye
Gansu Wuwei
Qingyang in Gansu province
Pingliang City
Dingxi, Gansu Province
Gansu Longnan
Yumen in Gansu province
Dunhuang in Gansu province
Linxia Gansu
Gannan, Gansu
Guangzhou, Guangdong
Shenzhen, Guangdong
Guangdong Qingyuan
Shaoguan Guangdong
Guangdong Heyuan
Guangdong Meizhou
Chaozhou Guangdong
Shantou, Guangdong
Guangdong Jieyang
Guangdong Shanwei
Huizhou, Guangdong
Dongguan, Guangdong
Guangdong Zhuhai
Zhongshan, Guangdong
Guangdong Jiangmen
Guangdong Foshan
Zhaoqing, Guangdong
Guangdong Yunfu
Yangjiang, Guangdong
Guangdong Maoming
Zhanjiang, Guangdong
Zhuang nationality in Nanning, Guangxi
Guangxi Zhuang Guilin
Guangxi Zhuang Liuzhou
Zhuang nationality in Wuzhou, Guangxi
Guangxi Zhuang nationality Guigang
Guangxi Zhuang Yulin
Guangxi Zhuang Qinzhou
Guangxi Zhuang Nationality Beihai
Guangxi Zhuang Fangchenggang
Guangxi Chongzuo Zhuang nationality
Guangxi Zhuang Baise
Guangxi Hechi Zhuang nationality
Guangxi Zhuang guests
Hezhou City, Guangxi Zhuang Autonomous Region
Guangxi Cenxi Zhuang nationality
Guangxi Zhuang Guiping
Beiliu City, Guangxi Zhuang Autonomous Region
Guangxi Zhuang Dongxing
Guangxi Zhuang nationality Pingxiang
Guangxi Yizhou Zhuang nationality
Zhuang nationality in Heshan, Guangxi
Guiyang Guizhou
Liupanshui Guizhou
Zunyi, Guizhou
anshun city guizhou province
Bijie City, Guizhou Province
Guizhou Tongren
Qingzhen of Guizhou
Guizhou Chishui
Guizhou renhuai
Guizhou Kaili
Duyun in Guizhou
Xingyi in Guizhou
Guizhou Fuquan
Haikou City, Hainan Province
Sanya, Hainan
Wenchang City, Hainan Province
Hainan qionghai city
Wanning City, Hainan Province
Dongfang city, Hainan
Danzhou City, Hainan Province
Wuzhishan City, Hainan
Shijiazhuang City, Hebei Province
Hebei Handan
Hebei Tangshan
Baoding City, Hebei Province
Qinhuangdao, Hebei province
Xingtai city, Hebei province
Zhangjiakou city, Hebei province
Hebei Chengde
Cangzhou Hebei
Hebei Langfang
Hengshui City, Hebei Province
Hebei xinji
Hebei Gaocheng
Jinzhou of Hebei province
Hebei Xinle
Hebei Luquan
Hebei zunhua
Qian' an, Hebei
Bazhou, Hebei Province
Hebei Sanhe
Dingzhou City, Hebei Province
Zhuozhou City, Hebei Province
Hebei anguo
Hebei Gaobeidian
Botou of Hebei Province
Renqiu City, Hebei Province
Hebei Huanghua
Hejian city, Hebei Province
Hebei Jizhou
Shenzhou, Hebei
Nangong City, Hebei Province
Shahe, Hebei
Wuan, Hebei
Henan Zhengzhou
Kaifeng, Henan
Luoyang, Henan
Pingdingshan, Henan
Anyang, Henan
Hebi of Henan province
Physical quotient
Jiaozuo in Henan province
Puyang, Henan
xuchang city, henan province
Luohe, Henan
Sanmenxia, Henan
Henan Nanyang
Henan Shangqiu
Henan Zhoukou
Zhumadian in Henan province
Xinyang, Henan
Jiyuan of Henan province
Gongyi, Henan
Dengzhou, Henan
Yongcheng, Henan
Ruzhou, Henan
Xingyang, Henan
Xinzheng, Henan
Dengfeng of Henan province
Xinmi, Henan
Yanshi in Henan province
Mengzhou, Henan
Qinyang, Henan
Henan weihui
Huixian County, Henan Province
Linzhou, Henan
Henan Yuzhou
Changge city, Henan Province
He' nan Wugang
Yima in Henan province
Henan Lingbao
Xiangcheng, Henan province
Harbin, Heilongjiang
Qiqihar in Heilongjiang
Heihe River, Heilongjiang Province
Daqing City, Heilongjiang Province
Yichun City, Heilongjiang Province
Hegang in Heilongjiang
Jiamusi, Heilongjiang
Shuangyashan in Heilongjiang
Qitaihe River in Heilongjiang Province
Jixi, Heilongjiang Province
Mudanjiang City, Heilongjiang Province
Heilongjiang suihua
Heilongjiang Shuangcheng
Heilongjiang Shangzhi
Heilongjiang Wuchang
Acheng of Heilongjiang
Nehe, Heilongjiang
Heilongjiang Beian
Heilongjiang Wudalianchi
Heilongjiang tieli
Heilongjiang Tongjiang
Heilongjiang Fujin
Hulin in Heilongjiang
Mishan, Heilongjiang
Suifenhe, Heilongjiang
Heilongjiang hailin
Ning 'an, Heilongjiang
Heilongjiang anda
Zhaodong of Heilongjiang Province
Heilongjiang Helen
Wuhan, Hubei
Shiyan in Hubei province
Xiangfan City, Hubei Province
Jingmen, Hubei
Xiaogan City, Hubei Province
Hubei Huanggang
Hubei Ezhou
Hubei Huangshi
Xianning, Hubei
Jingzhou Hubei province
Yichang, Hubei
Suizhou City, Hubei Province
Xiantao in Hubei
Hubei Tianmen
Qinjiang
Danjiangkou city, Hubei Province
Laohekou city, Hubei Province
Zaoyang, Hubei
Yicheng, Hubei
Hubei Zhongxiang
Hubei Hanchuan
Hubei Yingcheng
Anlu, Hubei
Hubei Guangshui
Macheng, Hubei
Wuxue City, Hubei Province
Hubei Daye
Hubei Chibi
Shishou City, Hubei Province
Honghu Lake, Hubei
Hubei Songzi
Hubei Yidu
Zhijiang of Hubei province
Dangyang in Hubei Province
Enshi, Hubei
Lichuan, Hubei
Changsha, Hunan
Hunan Hengyang
Zhangjiajie, Hunan
Changde, Hunan
Yiyang, Hunan
Yueyang, Hunan
Hunan Zhuzhou
Hunan Xiangtan
Chenzhou of Hunan province
Yongzhou of Hunan province
hunan shaoyang
Huaihua
Loudi in Hunan
Hunan Leiyang
Changning, Hunan
Liuyang of Hunan province
Jinshi, Hunan
Yuanjiang, Hunan
Hunan Miluo
Linxiang City, Hunan Province
Liling, Hunan
Hunan Xiangxiang
Shaoshan, Hunan
Hunan Zixing
Wugang, Hunan
Hongjiang, Hunan
Lengshuijiang, Hunan
Lianyuan, Hunan
Jishou City, Hunan Province
Jilin Changchun
Jilin City, Jilin Province
Jilin Baicheng
Jilin Songyuan
Siping City, Jilin Province
Liaoyuan City, Jilin Province
Jilin Tonghua
Jilin Baishan
Jilin dehui
Jilin jiutai
Jilin Yushu
Jilin Panshi
Jilin Jiaohe
Jilin Huadian
Shulan city, Jilin Province
Taonan city, Jilin Province
Jilin daan
Jilin Shuangliao
Jilin gongzhuling
Jilin Meihekou
Jilin Ji 'an
Jilin Linjiang
Yanji Jilin
Jilin Tumen
Jilin Dunhua
Jilin Hunchun
Jilin Longjing
Jilin Helong
Jiangsu Nanjing
Xuzhou Jiangsu
Lianyungang Jiangsu
Jiangsu Suqian
Huaian Jiangsu
Yancheng Jiangsu
Yangzhou Jiangsu
Jiangsu Taizhou
Jiangsu Nantong
Jiangsu Zhenjiang
Changzhou Jiangsu
Wuxi, Jiangsu
Suzhou, Jiangsu
Jiangsu jiangyin
Yixing Jiangsu
Pizhou of Jiangsu province
Jiangsu Xinyi
Jiangsu Jintan
Jiangsu Liyang
Jiangsu Changshu
Jiangsu zhangjiagang
Jiangsu Taicang
Kunshan Jiangsu
Jiangsu Wujiang
Rugao Jiangsu
Jiangsu Haimen
Jiangsu Qidong
Jiangsu Dafeng
Jiangsu Dongtai
Jiangsu Gaoyou
Jiangsu Yizheng
Jiangsu Yangzhong
Jiangsu jurong
Jiangsu Danyang
Jiangsu Xinghua
Jiangsu Jiangyan
Jiangsu Taixing
Jiangsu Jingjiang
Jiangxi Nanchang
Jiangxi Jiujiang
Jingdezhen in Jiangxi province
Jiangxi yingtan
Jiangxi Xinyu
Pingxiang City, Jiangxi Province
Jiangxi Ganzhou
Shangrao in Jiangxi
Jiangxi Fuzhou
Yichun of Jiangxi province
Ji ' an, Jiangxi
Jiangxi Ruichang
Jiangxi leping
Jiangxi ruijin
Jiangxi Nankang
Jiangxi Dexing
Jiangxi Fengcheng
Jiangxi Zhang Shu
Jiangxi gaoan
Jiangxi Jinggangshan
Jiangxi Guixi
Shenyang, Liaoning
Dalian Liaoning
Chaoyang Liaoning
Liaoning Fuxin
Tieling
Fushun Liaoning
Benxi Liaoning
Liaoyang Liaoning
Anshan Liaoning
Dandong Liaoning
Yingkou Liaoning
panjin
Jinzhou Liaoning
Huludao Liaoning
Liaoning Xinmin
Liaoning Wafangdian
Liaoning pulandian
Zhuanghe, Liaoning
Liaoning beipiao
Liaoning Lingyuan
Diaobingshan City, Liaoning Province
Liaoning kaiyuan
Liaoning lighthouse
Liaoning Haicheng
Liaoning Fengcheng
Liaoning Donggang
Liaoning Dashiqiao
Gaizhou city, Liaoning Province
Liaoning Linghai
Liaoning beining
Liaoning Xingcheng
Hohhot, inner Mongolia
Baotou, Inner Mongolia
Wuhai, Inner Mongolia
Inner Mongolia Chifeng
Hulunbeier, Inner Mongolia
Tongliao, Inner Mongolia
Ulanqab, Inner Mongolia
Inner Mongolia Erdos
Bayannur, Inner Mongolia
Manzhouli, Inner Mongolia
Zhalantun, Inner Mongolia
Yakeshi, Inner Mongolia
Genhe in Inner Mongolia
Ergon, Inner Mongolia
Ulanhot, Inner Mongolia
Aershan, Inner Mongolia
Holingol, Inner Mongolia
Xilinhot, Inner Mongolia
Erenhot, Inner Mongolia
Fengzhen, Inner Mongolia
Ningxia Hui Yinchuan
Ningxia Shizuishan Hui nationality
Ningxia Hui Autonomous Region
Ningxia Hui Zhongwei
Ningxia Hui Guyuan
Ningxia Hui Autonomous Region
Qingtongxia of Hui nationality in Ningxia
Xining in Qinghai
Qinghai Golmud
Delhi, Qinghai
shandong jinan
Qingdao Shandong
Shandong Liaocheng
Shandong Dezhou
Shandong Dongying
Shandong zibo
Shandong Weifang
Shandong Yantai
Shandong Weihai
Shandong Rizhao
Linyi, Shandong
Shandong Zaozhuang
Jining of Shandong province
Shandong Taian
Shandong Laiwu
Shandong Binzhou
Heze, Shandong
Shandong Zhangqiu
Shandong JIAOZHOU
Shandong Jiaonan
Shandong Jimo
Shandong Pingdu
Shandong laixi
Shandong Linqing
Shandong leling
Shandong Yucheng
Shandong anqiu
Shandong changyi
Shandong Gaomi
Shandong Qingzhou
Zhucheng of Shandong province
Shandong Shouguang
Shandong Qixia
Shandong Haiyang
Shandong longkou
Shandong Laiyang
Shandong Laizhou
Shandong Penglai
Shandong Zhaoyuan
Shandong Wendeng
Shandong Rongcheng
Rushan, Shandong
Tengzhou in Shandong province
Qufu in Shandong province
Shandong Yanzhou
Zoucheng City, Shandong Province
Shandong Xintai
Shandong feicheng
Taiyuan, Shanxi
Datong, Shanxi
Shuozhou Shanxi
Shanxi Yangquan
Shanxi Changzhi
Shanxi Jincheng
Shanxi Xinzhou
Shanxi Lvliang
Shanxi Jinzhong
Linfen Shanxi
Shanxi Yuncheng
Shanxi Gujiao
Shanxi Lucheng
Gaoping City, Shanxi Province
Shanxi yuanping
Shanxi xiaoyi
Shanxi fenyang
Shanxi Jiexiu
Houma in Shanxi
Shanxi Huozhou
Shanxi Yongji
Shanxi hejin
Shaanxi Xi' an
Yan 'an, Shaanxi
Shaanxi Tongchuan
Weinan of Shaanxi province
Xianyang of Shaanxi province
Baoji Shaanxi
Shaanxi Hanzhong
Shaanxi Yulin
Shaanxi Shangluo
Shaanxi Ankang
Shaanxi Hancheng
Shaanxi Huayin
Shaanxi Xingping
Chengdu, Sichuan
Sichuan Guangyuan
Mianyang, Sichuan
Deyang Sichuan
Sichuan Nanchong
Guang 'an Sichuan
Sichuan Suining
Neijiang in Sichuan
Leshan Sichuan
Zigong in Sichuan
Sichuan Luzhou
Yibin Sichuan
Sichuan Panzhihua
Sichuan Bazhong
Sichuan Dazhou
Ziyang in Sichuan
Sichuan Meishan
Yaan, Sichuan
Chongzhou in Sichuan
Sichuan Qionglai
Dujiangyan in Sichuan
Sichuan Pengzhou
Sichuan Jiangyou
Sichuan shifang
Sichuan Guanghan
Mianzhu in Sichuan
Sichuan Langzhong
Sichuan Huaying
Emeishan, Sichuan
Sichuan Wanyuan
Jianyang in Sichuan
Xichang in Sichuan
Taiwan Province Province has no statistics.
Lhasa, Tibet
Xigaze, Tibet
Xinjiang Uygur Urumqi
Xinjiang Uygur Karamay
Xinjiang Uygur Shihezi
Xinjiang Uygur Aral language
Xinjiang Uygur Tumushuke
Xinjiang Uyghur Wujiaqu
Xinjiang Uyghur Beitun
Xinjiang Uygur Kashi
Xinjiang Uygur Aksu
Xinjiang Uygur Hotan
Xinjiang Uygur Turpan people
Hami, Xinjiang Uygur nationality
Xinjiang Uygur Artux
Xinjiang Uygur Bole
Xinjiang Uygur Changji
Xinjiang Uygur Fukang
Xinjiang Uygur Miquan
Korla of Xinjiang Uygur nationality
Yining people of Xinjiang Uygur nationality
Kuitun, Xinjiang Uygur Autonomous Region
Xinjiang Uygur Tacheng people
Xinjiang Uyghur Wusu
Xinjiang Uygur Altay
Kunming, Yunnan
Yunnan Qujing
Yuxi Yunnan
Lijiang, Yunnan
Yunnan Zhaotong
Simao in Yunnan
Yunnan Lincang
Yunnan Baoshan
Yunnan Anning
Yunnan Xuanwei
Yunnan mangshi
Yunnan Ruili
Dali, Yunnan
Chuxiong in Yunnan
Gejiu in Yunnan
Yunnan kaiyuan
Yunnan Jinghong
Hangzhou, Zhejiang
Ningbo, Zhejiang
Sir Robert Giffen
Jiaxing Zhejiang
Zhoushan Zhejiang
Shaoxing, Zhejiang
Quzhou, Zhejiang
Zhejiang Jinhua
Zhejiang Taizhou
Wenzhou, Zhejiang
Lishui Zhejiang
Zhejiang Lin 'an
Zhejiang Fuyang
Zhejiang Jiande
Cixi, Zhejiang
Yuyao Zhejiang
Fenghua Zhejiang
Zhejiang Pinghu
Haining Zhejiang
Zhejiang Tongxiang
Zhuji Zhejiang
Shangyu Zhejiang
Zhejiang shengzhou
Zhejiang Jiangshan
Zhejiang Lanxi
Zhejiang Yongkang
Yiwu Zhejiang
Zhejiang dongyang
Zhejiang Linhai
Wenling Zhejiang
Zhejiang ruian
Zhejiang yueqing
Zhejiang Longquan
Beijing beijing
Tianjin Tianjin
Shanghai! Force … to do …; (Shanghai) Shanghai
Chongqing Chongqing
Hong kong hong kong
Macao Macao