Question about Excel

kaixin123

感谢bats童鞋的帮助，感谢你特意帮忙写公式，谢谢！

Best answer so far in level 9! Thanks Jasonliu234 ^_^

I would like to split the suburb name and street/rd/ave from the address given. Would anyone have an idea how to solve this? I've tried couple of formular thing, and haven't work out a way.

I knnow some professionals here are expert in Excel. Look forward your reply

Thanks in advance!

example, i have following address in excel and need to split them. Total more than 10k addresses.

CNR CREEK & LOGAN RD     MT GRAVATT
CNR VESPER & NORTH ST    BATEMANS BAY
CNR WILCOCKS AVE         NAROOMA
CNR GILCHRIST AVE        CAMPBELLTOWN
CNR HINDMARSH RD         WODEN
TERINDALE DRIVE          TUGGERANONG
CLARINDA ST              PARKES
1 WOOLWORTHS WAY BELLA        VISTA
171 CAMBERWELL                CAMBERWELL
130 PORT STEPHENS ST          RYMND TER
22 CULLOTON CRS               BALGA
2021 WYNNUM RD                WYNNUM
KURRAJONG ROAD           CARNES HILL
LEISURE & FRASER DRV          BANORA POINT
U LOT 1 COWPASTURE RD    HOXTON PARK

[ 本帖最后由 kaixin123 于 2012-7-24 00:32 编辑 ]

umehomes

Ty use Data - Text to Column - Select "deliminited" - 'Comma" and "Space" and see how it works.

jasonliu234

office 2013 -  flash fill

flat26a

原帖由 umehomes 于 2012-7-23 12:54 发表
Ty use Data - Text to Column - Select "deliminited" - 'Comma" and "Space" and see how it works.

Space 不行的，会把路名中间有Space的地方也断开

kaixin123

Thanks above all for your quick response.

I did tried to split into deliminited, and it has the problem mention above by "flat26a". Thanks anyway ^_^

i would like all suburb in the same coloumn

leymoom

try google with "Split Data Into Columns in excel"

kaixin123

and I will compare street thing later on

youngf

You can use Macro.

jasonliu234

用fixed width就行

jasonliu234

不过还是office 2013强大。很直观，瞬间搞定

kaixin123

Thanks Jasonliu234, it works in most of case.

we are using Excel 2007 at the moment, not sure about office 2013, it sounds very powerful, will study later

thanks for your advice!

dingding.ann

没用过2013的飘过~~~话说看楼上的评论貌似2013很强大。

jasonliu234

这只是 合并 不同单元格

http://officepreview.microsoft.c ... 13-VA102834141.aspx

也可以 拆分 出两个单元格。

根据左边单元格的Pattern 自动拆分

[ 本帖最后由 jasonliu234 于 2012-7-23 14:40 编辑 ]

rogerze5046

2013很强大！

binbenban

bats

空位规则的话，9楼同学的fixed width就行了。否则的话，要完美，得弄个dictionary column把澳洲全suburb名字放进去，然后delimited按空格分后，用公式或macro match suburb来区分。当然还有问题，就是一个suburb名字可能是另一个的一部分，所以要先把suburb名称按字母+长度排列后有条件vlookup区分

kaixin123

Hi "Binbenban" * bats":

Thanks for your comments. To be honest, the "space" in cells are not regular, sometimes it may have incomplete suburb name as well which is very messy. The method in level 9 sloved most of the problem, however, still certain percentage( i would say about 10%) incorrect and has to manually adjusted for this part of data.

if someone knows a better way and could let me know, i would be much appreciated

or there is another way, I just have to manually adjust them?(paopaobing(58))

大饼

我认为16楼的方案最好

euroblue

原帖由 bats 于 2012-7-23 16:48 发表
空位规则的话，9楼同学的fixed width就行了。否则的话，要完美，得弄个dictionary column把澳洲全suburb名字放进去，然后delimited按空格分后，用公式或macro match suburb来区分。当然还有问题，就是一个suburb名字可能是另一个的一部分，所以要先把suburb名称按字母+长度排列后有条件vlookup区分

这个是目前最精确的方法了。不过还有问题，就是 suburb 名正好是街名时不知道要怎么办。 eg. Burwood - 123 Burwood Rd

jwan142

用FORMULA 试试？
在B1里输入下面

=RIGHT(a1,LEN(a1)-FIND("  ",a1)-1)

注意FIND（后面是两个空格。你可以放3个。

jasonliu234

我又找了偷懒的办法
如果 lz的数据中，街名 和  suburn 中间有2个或2个以上的空格

就可以这么办：

bats

原帖由 euroblue 于 2012-7-23 17:57 发表



这个是目前最精确的方法了。不过还有问题，就是 suburb 名正好是街名时不知道要怎么办。 eg. Burwood - 123 Burwood Rd

这个没问题，因为match的时候是从后面的word向前match，matched后就不用搜了。所以字典suburb名字要先排序

jasonliu234

原帖由 jwan142 于 2012-7-23 18:23 发表
用FORMULA 试试？
在B1里输入下面

=RIGHT(a1,LEN(a1)-FIND("  ",a1)-1)

注意FIND（后面是两个空格。你可以放3个。

呵呵 跟我想的一样

bats

原帖由 jasonliu234 于 2012-7-23 18:29 发表


呵呵 跟我想的一样

这样的前提是空格规则分布。但是lz 例子里面已经有不规则的了，注意第八行Bella vista是一个区

[ 本帖最后由 bats 于 2012-7-23 18:44 编辑 ]

jasonliu234

原帖由 bats 于 2012-7-23 18:35 发表

这样的前提是空格规则分布。但是lz 例子里面已经有不规则的了，注意第八行Bella vista是一个区

就看有多少的空格是不规则的

如果空格不规则很多，那么vlookup 也是无用的。

bats

原帖由 jasonliu234 于 2012-7-23 18:58 发表


就看有多少的空格是不规则的

如果空格不规则很多，那么vlookup 也是无用的。

直接当然无效，先delimited再vlookup可以。那时候每个空格已经分开了

jasonliu234

原帖由 bats 于 2012-7-23 19:06 发表

直接当然无效，先delimited再vlookup可以。那时候每个空格已经分开了

就算每个单词都分开，如何才知道 哪个是 street 哪个是suburb？

jasonliu234

而且 lz 的 suburb名字，本身并不严谨

比如第一行 mt gravatt

但是suburb，正式名字 却是 Mount Gravatt

vlookup根本找不到

bats

原帖由 jasonliu234 于 2012-7-23 19:09 发表


就算每个单词都分开，如何才知道 哪个是 street 哪个是suburb？

从后往前，根据最长suburb名字match，所以要字典

bats

原帖由 jasonliu234 于 2012-7-23 19:15 发表
而且 lz 的 suburb名字，本身并不严谨

比如第一行 mt gravatt

但是suburb，正式名字 却是 Mount Gravatt

vlookup根本找不到

的确如此。这个属于raw data问题没办法。但是如果经过观察，或许可以全部replace再match，因为可能mt都可以改成mount，或者设立简写词条加入进去