會翔
Lv 5
會翔 發問時間: 電腦與網際網路程式設計 · 1 0 年前

請問EXCEL函數與巨集取特定字串(地區.路.街.號)

想請問知識+的高手

假設小弟有幾百筆資料 要處理

要把"地區" 與 "路名.街名"還有"號" 分別給取出來

例如

台北市大安區小明一街10號

台北市大安區小明二街20號

台北市大安區小英路30號

高雄市新興區小英五路50號

高雄市三民區小英十五路100號

我要取出來的字串

第一格是

台北市大安區

台北市大安區

台北市大安區

高雄市新興區

高雄市三民區

第二格是

小明一街

小明二街

小英路

小英五路

小英十五路

號碼在第三格

10號

20號

30號

50號

100號

這樣解說可能您不太了解 我補一張圖

http://i702.photobucket.com/albums/ww21/sp03154/03...

我有使用過MID跟IF的函數去做判斷處理,但是都無法正確處理

不知道高手您有沒有更好的方法,又或者可以幫我做一個巨集程式

謝謝您 感謝您

已更新項目:

抱歉 有個小問題 想請問高手您

補充

因為可能路跟街的後面

還要再判斷巷與弄的問題

如果 還要再判斷 "巷與弄" 不知道會不會比較麻煩呢?

http://i702.photobucket.com/albums/ww21/sp03154/03...

2 個已更新項目:

感謝 rc08@ymail.com 大大回答我的問題

謝謝您 感謝您

小弟剛剛試用了 真是太好用了

但是如果同時有"巷"跟"弄"

"弄"的部分會跑到"號"那格去

3 個已更新項目:

那我還有個問題想要請問您

如果在加一個判斷式"里"

也就是 "高雄市三民區仁愛里小英十五路30巷50弄100號"

分別取出 "市區" , "里" , "路" ,"巷" , "弄" ,"號"

這樣會很麻煩嗎?

再次謝謝您 感謝您

4 個已更新項目:

"街"跟"路" 在同一格 一起做判斷

位置在里之後

5 個已更新項目:

標頭欄位:

A    B C   D  E  F G  H  I

客戶地址  地區  里 街/路 巷 弄 號碼 Map

沒錯!! 謝謝您 感謝您

6 個已更新項目:

好棒 非常好用

幸好有高手您的協助

讓小弟跪拜一下

謝謝您 感謝您

4 個解答

評分
  • 1 0 年前
    最佳解答

    我估這題用函數會很長,不很實用,不如用巨集來得清析。

    這條巨集是臃腫了一點,好在讓你易於修改。

    A B C D E F G 客戶地址

    地區 街/路 巷/弄 號 地圖 台北市大安區小明一街10號

    台北市大安區 小明一街

    10號 Map 台北市大安區小明二街20號

    台北市大安區 小明二街

    20號 Map 台北市大安區小英路30號

    台北市大安區 小英路

    30號 Map 高雄市新興區小英五路50號

    高雄市新興區 小英五路

    50號 Map 高雄市三民區小英十五路100號

    高雄市三民區 小英十五路

    100號 Map 高雄市三民區小英十五路90弄100號

    高雄市三民區 小英十五路 90弄 100號 Map 高雄市三民區小英十五路2巷100號

    高雄市三民區 小英十五路 2巷 100號 Map 小英十五路2巷100號

    小英十五路 2巷 100號 Map

    Sub splitAddress()

    Dim parts, rest, Dist, Road, Lane, Num

    Dim startX, X

    startX = 2 '如果起始資料列不是 2 ,就修改這個數字。

    For X = startX To [A65536].End(xlUp).Row

    rest = Cells(X, 1)

    If InStr(rest, "區") > 0 Then

    parts = Split(rest, "區")

    Dist = parts(0) & "區"

    rest = parts(1)

    Else

    Dist = ""

    End If

    If InStr(rest, "街") > 0 Then

    parts = Split(rest, "街")

    Road = parts(0) & "街"

    rest = parts(1)

    ElseIf InStr(rest, "路") > 0 Then

    parts = Split(rest, "路")

    Road = parts(0) & "路"

    rest = parts(1)

    Else

    Road = ""

    End If

    If InStr(rest, "巷") > 0 Then

    parts = Split(rest, "巷")

    Lane = parts(0) & "巷"

    Num = parts(1)

    ElseIf InStr(rest, "弄") > 0 Then

    parts = Split(rest, "弄")

    Lane = parts(0) & "弄"

    Num = parts(1)

    Else

    Lane = ""

    Num = rest

    End If

    Cells(X, 3) = Dist

    Cells(X, 4) = Road

    Cells(X, 5) = Lane

    Cells(X, 6) = Num

    Cells(X, 7) = "Map"

    Next X

    End Sub

    2009-03-15 21:28:07 補充:

    這巨集,

    如果原地址沒有"區" ,則"區"欄會留空。

    如果原地址沒有"街"或"路" ,則"街/路"欄會留空。

    如果原地址沒有"巷"或"弄" ,則"巷/弄"欄會留空。

    不知 Map 要連結去那裡,留待你自己做,若須幫忙,就補充問題。

    2009-03-15 21:53:26 補充:

    試試這行 Cells(X, 7) = "Map"

    改為:

    ActiveSheet.Hyperlinks.Add Cells(X, 7), Address:="http://tw.maps.yahoo.com/?ei=utf8&addr=" & Cells(X, 1).Value, SubAddress:="", TextToDisplay:="MAP"

    2009-03-15 22:28:36 補充:

    "街" 又如何?

    是獨立一欄?在"里"之前還是之後?

    2009-03-15 23:29:25 補充:

    ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

    從新來過。

    標頭欄位:

    A    B C   D  E  F G  H  I

    客戶地址  地區  里 街/路 巷 弄 號碼 Map

    2009-03-15 23:36:57 補充:

    Sub splitAddress()

    Dim parts, rest, Qu, Li, Lu, Xiang, Lung, Num

    Dim startX, X

    startX = 2 '如果起始資料列不是 2 ,就修改這個數字。

    For X = startX To [A65536].End(xlUp).Row

    rest = Cells(X, 1)

    2009-03-15 23:37:17 補充:

    If InStr(rest, "區") > 0 Then

    parts = Split(rest, "區")

    Qu = parts(0) & "區"

    rest = parts(1)

    Else

    Qu = ""

    End If

    2009-03-15 23:37:34 補充:

    If InStr(rest, "里") > 0 Then

    parts = Split(rest, "里")

    Li = parts(0) & "里"

    rest = parts(1)

    Else

    Li = ""

    End If

    2009-03-15 23:37:47 補充:

    If InStr(rest, "街") > 0 Then

    parts = Split(rest, "街")

    Lu = parts(0) & "街"

    rest = parts(1)

    ElseIf InStr(rest, "路") > 0 Then

    parts = Split(rest, "路")

    Lu = parts(0) & "路"

    rest = parts(1)

    Else

    Lu = ""

    End If

    2009-03-15 23:37:58 補充:

    If InStr(rest, "巷") > 0 Then

    parts = Split(rest, "巷")

    Xiang = parts(0) & "巷"

    rest = parts(1)

    Else

    Xiang = ""

    End If

    2009-03-15 23:38:08 補充:

    If InStr(rest, "弄") > 0 Then

    parts = Split(rest, "弄")

    Lung = parts(0) & "弄"

    Num = parts(1)

    Else

    Lung = ""

    Num = rest

    End If

    2009-03-15 23:38:27 補充:

    Cells(X, 3) = Qu

    Cells(X, 4) = Li

    Cells(X, 5) = Lu

    Cells(X, 6) = Xiang

    Cells(X, 7) = Lung

    Cells(X, 8) = Num

    Cells(X, 9) = "Map"

    2009-03-15 23:38:32 補充:

    ActiveSheet.Hyperlinks.Add Cells(X, 9), Address:="http://tw.rd.yahoo.com/referurl/knowledge/maps/*ht... & Cells(X, 1).Value, SubAddress:="", TextToDisplay:="MAP"

    Next X

    End Sub

    2009-03-16 00:02:24 補充:

    ●●●●●●●●●●●●●●●●●●●●●●●●●●●

    ●●●●●●●●●●●●●●●●●●●●●●●●●●●

    簡化一下,將上面五個判斷區塊改為:

    Qu = Left(rest, InStr(rest, "區"))

    rest = Replace(rest, Qu, "")

    Li = Left(rest, InStr(rest, "里"))

    rest = Replace(rest, Li, "")

    2009-03-16 00:02:30 補充:

    Lu = Left(rest, InStr(rest, "街"))

    If Lu = "" Then Lu = Left(rest, InStr(rest, "路"))

    rest = Replace(rest, Lu, "")

    Xiang = Left(rest, InStr(rest, "巷"))

    rest = Replace(rest, Xiang, "")

    Lung = Left(rest, InStr(rest, "弄"))

    rest = Replace(rest, Lung, "")

    Num = Replace(rest, Lung, "")

  • 匿名使用者
    7 年前

    線上遊戲天堂 http://ts999.tw

    運動遊戲

    實況棒球、籃球、足球、冰球、網球等多種經典賽事

    即時遊戲

    百家、21點、牌九、二八杠、骰寶、龍虎、輪盤、牛牛、番攤

    對戰遊戲

    麻將、德州撲克、二八杠、吹牛、鬥地主、13支、暗棋、接龍

    電子遊戲

    5PK、7PK、水果盤遊戲、5輪遊戲、極限飆速、趙雲救主、西遊戲、金瓶梅、中國美人、英雄

    彩球遊戲

    大樂透、六合、時時彩、今彩539、3星彩、4星彩

    最新優惠活動

    1. 運動遊戲連過5關,彩今再加碼10%

    2. 新會員加入,有機會獲得500元禮券與精美讀卡機

    3. 遊戲積分可兌換汽機車、3C、精品等多種大獎

    免費線上體驗試玩喔~!

    線上遊戲天堂 http://ts999.tw

  • 1 0 年前

    一>你的需求應很簡單,假設你的資料結構與需求是如下:

      A   B   C   D   E

    1地址 市區鄉鎮 路街巷 號樓

    我的作法:假設A2~是你的資料地址欄位

    1在B2輸入公式=MID(A2,1,IF(ISERROR(FIND("鄉",A2))=TRUE,IF(ISERROR(FIND("鎮",A2))=TRUE,IF(ISERROR(FIND("區",A2))=TRUE,FIND("市",A2),FIND("區",A2)),FIND("鎮",A2)),FIND("鄉",A2)))

    2在C2輸入公式=MID(A2,1+LEN(B2),IF(ISERROR(FIND("巷",A2))=TRUE,IF(ISERROR(FIND("街",A2))=TRUE,FIND("路",A2),FIND("街",A2)),FIND("巷",A2))-LEN(B2))

    3在D2輸入公式=MID(A2,1+LEN(B2)+LEN(C2),99)

    4再將上述B2:D2的公式向下複製至你的資料範圍即可

    註:已將各縣有可能出現的鄉鎮及巷均已考慮在公式中了..

    二>假設你的資料結構與需求是如下:

      A  B C  D E  F G H

    1地址 縣市 區 鄉鎮 里 路街 巷 號

    我的作法:假設A2~是你的資料地址欄位,B1:H1是你要將地址拆解的項目,我的作法如下:

    1在B2輸入公式=IF(AND(ISERROR(FIND("縣",$A2)),ISERROR(FIND("市",$A2))),"",MID($A2,1,IF(ISERROR(FIND("縣",$A2))=TRUE,FIND("市",$A2),FIND("縣",$A2))))

    2在C2輸入公式=IF(ISERROR(FIND("區",A2))=TRUE,"",MID($A2,1+LEN(B2),FIND(C$1,A2))-LEN(B2))

    4在D2輸入公式=IF(AND(ISERROR(FIND("鄉",$A2)),ISERROR(FIND("鎮",$A2))),"",MID($A2,1+SUMPRODUCT(LEN($B2:$C2)),IF(ISERROR(FIND("鄉",$A2))=TRUE,FIND("鎮",$A2),FIND("鄉",$A2))-SUMPRODUCT(LEN($B2:$C2))))

    3在E2輸入公式=IF(ISERROR(FIND(E$1,$A2)),"",MID($A2,1+SUMPRODUCT(LEN($B2:$D2)),FIND(E$1,$A2)-SUMPRODUCT(LEN($B2:$D2))))

    4在F2輸入公式=IF(AND(ISERROR(FIND("街",$A2)),ISERROR(FIND("路",$A2))),"",MID($A2,1+SUMPRODUCT(LEN($B2:$E2)),IF(ISERROR(FIND("街",$A2))=TRUE,FIND("路",$A2),FIND("街",$A2))-SUMPRODUCT(LEN($B2:$E2))))

    5在G2輸入公式=IF(ISERROR(FIND(G$1,$A2)),"",MID($A2,1+SUMPRODUCT(LEN($B2:$F2)),FIND(G$1,$A2)-SUMPRODUCT(LEN($B2:$F2))))

    6在H2輸入公式=IF(ISERROR(FIND(H$1,$A2)),"",MID($A2,1+SUMPRODUCT(LEN($B2:$G2)),LEN($A2)-SUMPRODUCT(LEN($B2:$G2))))

    4再將上述B2:H2的公式向下複製至你的資料範圍即可

    若還有問題,歡迎來信洽詢jokercheng.tw@yahoo.com.tw

    參考資料: 自己
  • 夏日
    Lv 5
    1 0 年前

    類似的問題

    http://tw.knowledge.yahoo.com/question/question?qi...

    其他的請自行研究修改了。

    2009-03-15 15:17:25 補充:

    巷與弄的問題最簡單的寫法就是取出這三欄後用SUBSTITUTE取代掉就是結果了。

還有問題?馬上發問,尋求解答。