1 2 3 4

廣告

網友您好,若是您覺得我寫的工具或文章對您有幫助,
而您或者您的朋友有在博客來購物,可以透過底下連結

如何在博客來購物贊助網站或是點選計數器下方的博客來圖示來讓網站可以永續經營。

2008年7月16日 星期三

如何在Excel一次取代多個字串

Excel有取代的功能,但是如果您要一次取代很多筆資料,就會覺得很不方便,所以我就寫了一個VBA,只要把要取代的內容一次寫完,按下開始整批取代,就會一次把您要取代的所有動作都作完。
練習檔案:
2009/9/30新增所有活頁都會取代
1.假設我要取代4種資料 2. 將batchReplace.xls跟您要取代的檔案放在同一個目錄, 只要執行batchReplace.xls(要取代的檔案會自動開啟) 3.設定您要批次取代的內容 4.執行取代完的對話框 5.程式碼一覽
Sub Macro1()

    Dim nowworkbook As String   '參數設定的檔案名稱
    Dim s   '要被取代的字串
    Dim d   '取代過的字串
  
    nowworkbook = Excel.ActiveWorkbook.Name

    Workbooks.Open Filename:=Excel.ActiveWorkbook.Path & "\" & Cells(2, 1) & ".xls"
  
    i = 2
  
    While Windows(nowworkbook).ActiveSheet.Cells(i, 2) <> ""
        s = Windows(nowworkbook).ActiveSheet.Cells(i, 2)
        d = Windows(nowworkbook).ActiveSheet.Cells(i, 3)
  
        Cells.Replace What:=s, Replacement:=d
        i = i + 1
    Wend
    Cells(1, 1).Select
    Windows(nowworkbook).Activate
    MsgBox "取代完成,請切視窗查看結果"
End Sub

68 則留言:

史上最強笨蛋 提到...

大大您好~小弟想藉由此篇程式碼導引學生學習VB,望大大首肯,謝謝!!

一整天 提到...

史上最強笨蛋:
您可以拿去做教學沒問題.

史上最強笨蛋 提到...

感恩~謝謝您!!

乖同事 提到...

大大可以跟泥請教一下:
小弟有個問題請教,目前sheet 1已建立搜尋範圍1,(A廠商)A3,b20 2.(b廠商)d3,e20且已定義範圍,下面各有2欄分別為規格及品名.如何在sheet 2我如何只輸入規格,例:001,而出現品名:紅色(品名為文字),目前卡在使用vlookup只能選擇一個定義範圍廠商A,無法選到廠商B,若不清楚小弟在補上,希望大大能幫幫我,感激不盡,啊謝謝.

一整天 提到...

乖同事您好:
我幫您試過跟找過Vlookup的搜尋範圍不能是兩個以上的範圍,惟一的解決方法,就是找一個sheet或範圍,利用公式=把這兩個範圍變成一個連續的範圍,再利用vlookup去查詢就可以.

乖同事 提到...

小弟附上檔案希望大大能形容的詳細一點,感激不盡,所謂的連續範圍是什麼...望大大指點.

http://www.badongo.com/file/17421084

一整天 提到...

乖同事您好:
方便寫email到我的信箱嗎?我將改過的Excel寄給您.

乖同事 提到...

這是我的信箱,
ilsfh1.tw@yahoo.com.tw

一整天 提到...

乖同事您好:
我己經將檔案寄給您了.

Horace 提到...

大大您好
我有試用了
單一檔案的大量取代變得很方便
也非常感謝大大您的分享

想請問的是,這能否做多檔案的大量取代多字串呢?
因為我試了一下,發現一次只能處理單一檔案
batchreplace_v2.xls的檔案名稱下方列了兩個以上的檔案名稱,依然只能處理第一個檔案

一整天 提到...

Horace您好:
您可以到首頁右上方,選[範例檔案下載]/[範例程式]/找到batchreplace_v3.xls,這個檔案的功能就是你要的.

Horace 提到...

大大,非常感謝您願意回應我們這些無禮的需求
v3版本我試用了
不知道是不是我自己電腦的問題
因為按下「開始整批取代」後
彈出了confirm box寫著:檔案「replacetest.xls已經開啟,重新開啟它將會導致您所做的修改通通遺失。您要重新開啟replacetest.xls嗎?」
若按下「是」則會開啟replacetest.xls並且不斷彈出相同的confirm box(即都寫說要重開replacetest.xls檔)
若按下「否」則會出現另一個對話方塊寫著:
「執行階段錯誤'1004': 'Open'方法(Workbooks物件)失敗」
若再按下此對話方塊的「偵錯」按鈕,則會再VB的IDE,並跳到下列這行
Workbooks.Open Filename:=Excel.ActiveWorkbook.Path & "\" & strFileName

請問是不是小弟哪裡設定錯了呢?

一整天 提到...

Horace您好:
是程式的問題,我己將程式修改過.麻煩您再重新下載試看看.

Horace 提到...

大大非常感謝您
我測試過已經可以了
非常好用,太感謝您了
謝謝!!

nax 提到...
作者已經移除這則留言。
nax 提到...

大大你好,請問一些問題
1.如果要將1列取代成3列的話~要怎麼改巨集呢?
ex:將1取代1 2 3(分成3列)
2.在取代時是不是無法判斷成字串~
ex:11會被取代成aa而不是原本要設定的值
以上問題麻煩大大幫忙
感激不盡~

一整天 提到...

nax您好:
第1個問題,我不太懂您要做的.(您可以試看看Excel的取代有變法做到嗎?如果可以的話,就有辦法達到)
第2個問題,我是設定取代部分.您可以把它改成儲存格完全相等才取代,就不會有您說的問題出現.
程式修改如下:
Cells.Replace What:=s, Replacement:=d
改成
Cells.Replace What:=s, Replacement:=d, LookAt:=xlWhole

hsing-chuan 提到...

大大,非常感謝您提供的巨集,相當好用。但我剛才試用的結果,發現取代後的結果會有點小錯誤,例如0101本來要用「第10條第1項」的文字取代,但它顯示的結果卻只有「第10條1」,這會是什麼原因呢?請賜教,謝謝!

一整天 提到...

hsing-chuan您好:
我這邊試不會出現您說的情形,方便寄給我有問題的檔案.我這邊試看看.

hsing-chuan 提到...

是的,感謝你大力幫忙,可我在網站上看不到你的email呢?

一整天 提到...

Melvin_Lin您好:
我的Email在首頁右上方就可以看到.

hsing-chuan 提到...

網頁右上方的確有看到email的字,但是否是圖形?因為我看不到address,只出現一個X。

一整天 提到...

hsing-chuan您好:
是圖片沒錯,您把信件寄到imingho@gmail.com

hsing-chuan 提到...

謝謝!我已經把檔案寄給你了。

一整天 提到...

hsing-chuan您好:
  我看過您的問題,是部分比對跟完全比對的問題,我回email給您了,我己經把這支程式修改過了.相關連結請看:
http://272586.blogspot.com/2010/04/excelexcel.html

偲萍 提到...

非常感謝大大提供了許多便利的程式。
但在使用時遇到些問題,希望能得到大大的解答。
在取代多個非常長的字串時,Excel內建功能會出現"公式太長"的視窗,而使用你這檔案,也是無法取代。

例如:
字串為A1000|A1001|A/1002|A1003。。。(至A1050),並且會出現在多個儲存格之內
想把"A/"都取代為"A",內建取代功能便會出現公式太長。

希望大大能了解我的意思,並且冒昧的希望大大能幫幫忙。

一整天 提到...

偲萍您好:
我這一支程式是利用Excel內建功能,所以一樣會出現錯誤問題,您可以將檔案寄給我.我看看可不可以利用Excel的VBA解決您的問題。

提姆的家 提到...

您好,

小弟都以excel來維護資料庫, 目前資料筆數有400多筆, 以往要修改大筆相同內容, 都以"編輯"-->"尋找"-->"全部取代", 做資料庫的大批次修改, 但是可能資料庫數量越來越龐大, 現在執行"全部取代", 都會說"公式太長", 而無法修改, 就算逐條修改, 也多會產生"公式太長", 有哪一個大大可以幫小弟解決此問題, 感激不盡!

舉例, 搜尋到200筆的
, 要全刪掉, 因此讓"全部取代"留空白, 並執行全部取代, 但產生"公式太長", 無法執行!

有人說您在這方面很強, 是否可以教我如何簡單又快速的修改這些問題, 謝謝您!

一整天 提到...

提姆的家您好:
可以寄給我您有問題的Excel範例檔案,我再幫您看看有沒有其他解法.

xiecun 提到...

一整天大大您好,想請問一下,請問有辦法增加Excel的文字方塊內的文字取代功能嗎?
(正確的說,是快取圖案內新增的文字)

因為工作上的Excel檔案中,儲存格的內文字都可以取代,但就是快取圖案內的文字還是得一一手動作業.....

一整天 提到...

xiecun您好:
您到底下連結下載檔案即可,這版加入您要的功能,可以取代文字方塊內的文字.

https://sites.google.com/site/272586/fan-li-cheng-shi/batchreplace_v4.xls?attredirects=0&d=1

xiecun 提到...

一整天大大,感謝您的大力幫忙。文字方塊的的文字也可以取代了。我之前想要用Excel內的取代功能取代文字方塊內的文字,沒想到只要點了文字方塊,取代的功能就反灰,只能呆呆的一個一個去貼上。您真的是太強了。

林聖翔 提到...

大大您好,很感謝您的文檔,非常實用^^

不過小弟這兩天遇到些問題
就是有些檔案可以正常使用
有些則是會跳出
「執行階段錯誤 13 型態不符合」的狀況

錯誤程式碼是
「Cells.Replace What:=s, Replacement:=d, LookAt:=xlWhole」

我有拍下畫面,可以請大大幫忙看一下嗎>"<

http://i.imgur.com/BFYoW.jpg

林聖翔 提到...

阿,我發現原因了
如果要替換的儲存格中
有使用過「ALT+ENTER」換行的話
就會導致巨集執行失敗Q_Q

一整天 提到...

林聖翔您好:
我這邊試不會有您說的問題,您可以將檔案寄給我.我找看看.如果可以我會加個判斷提醒.

小晨 提到...
作者已經移除這則留言。
小晨 提到...

感謝大大的分享~~
請問除了取代文字外,
可以包含格子顏色或文字顏色嗎??

一整天 提到...

小晨:
您要的功能改好了.請到
http://discuz.bestdaylong.com/thread-214-1-1.html
下載

程式爛真是苦惱 提到...

一整天大大您好
我有使用您的這個excel去做批次取代,不過遇到了一點小麻煩,想要請教一下,由於我想要做數字的取代,但我的檔案有到像1-30856這樣的數字範圍,但我只想要取代掉像5、85、3085這樣的數字的時候,30856中的5或85或3085也會被取代掉,我要怎麼做才能讓excel知道我只是要取代5、85、3085,而不是所有包含5的數字,謝謝!

一整天 提到...

程式爛真是苦惱您好,
請到
http://discuz.bestdaylong.com/thread-239-1-1.html
下載最新版本.
2012/8/5 加入"全半型需相符"選項
2012/7/19 加入"大小寫需相符"選項 2012/7/10 修改可以取代格式的程式

程式爛真是苦惱 提到...

OK~謝謝您
目前還在等待驗證中

xiecun 提到...
作者已經移除這則留言。
xiecun 提到...

一整天大大,我發現新的V4版,當用於取代有儲存格內有註解的文件時,會出現 『 執行階段錯誤(後面接一串數字),'Select'方法('Shape'物件)失敗」的訊息,取代會失敗,請您協助確認看看

一整天 提到...

xiecun,
您可以到底下載新版的試看看.

如何在Excel一次取代多個字串
http://discuz.bestdaylong.com/forum.php?mod=viewthread&tid=239&fromuid=2

xiecun 提到...

您好,我有測試過,當儲存格的註解是顯示的狀態,可以正常動作。但是如果儲存格的註解是被隱藏的狀態時,一樣會出現錯誤訊息。

不好意思,麻煩您確認一下,謝謝。

一整天 提到...

xiecun,
己經修正您說的問題,麻煩您到底下載新版.

如何在Excel一次取代多個字串
http://discuz.bestdaylong.com/forum.php?mod=viewthread&tid=239&fromuid=2

xiecun 提到...

謝謝大大的協助,測試OK沒有問題了,感謝。

Alex Kuan 提到...

一整天大你好:
看了你的部落格茲後受用無窮,因使用上目前有一些特殊需求還想在此請教,就是基於V4版本的取代,不知道是否可以執行後將我要需要取代的原始字串反白起來,然後將要取代的字串放置在新的格子中。

簡單來說我的B1~B4內的字串需要取代,執行程式後會將B1~B4反白或上色,然後將要取地的新字串顯示在A1~A4,不知道這樣是否可行呢?

一整天 提到...

Alex Kuan您好,
我是用Excel的取代功能,所以Excel取代功能中沒有的功能,這支程式就不會有.

法蘭克 提到...

請問大大
我該如何在尋找到相同資料的儲存格後
變更他的向右一格儲存格的值呢?
我透過您的S去變更
好像仍然還是只會改到對應的那一格儲存格
再麻煩大大賜教 感謝~

一整天 提到...

法蘭克您好,
我是用Excel的取代功能,所以Excel取代功能中沒有的功能,這支程式就不會有,您的問題只能自己寫ExcelVBA去解決。

法蘭克 提到...

了解 我再想辦法 謝謝大大 ^^

迪 P 提到...

一整天大你好:
因有此需求,下載V3版本的取代功能,超級受用,只是有一問題相要請教。
因我有一欄格式是05-2013,但取代後它就會自動判斷為日期格式變成2013/05/01,不知要修改哪段才能顯示成我需求的格式?故來留言指教,感謝惠覆。

一整天 提到...

迪 P 您好,
請問您用Excel的取代功能也會有一樣的情形嗎?

迪 P 提到...

一整天大你好:
我試過了,不會耶!!05-2013取代成06-2013,不會變成2013/6/1

一整天 提到...

迪 P 您好,
我試了只要用Excel VBA取代就會有這個問題,看起來是Excel問題,所以我會建議您用二段式取代,第一段(用程式)先把05-2013取代成DD-06-2013,第二段(手動)將DD取代成空白.

迪 P 提到...

了解,感謝您的惠覆,這是個好論壇,請繼續加油喔~

hemilun 提到...

感謝你,讓我能快速處理一萬多筆資料,節省好多 時間

Emily Shin 提到...

大大 偶然間發現您寫的程式,這對我們翻譯人員真是一大福音。
我跟excel實在太不熟了,試著用過後發現,似乎"欄位的內容必須要完全相符才能夠取代成功",無法取代欄位中的部分內容。
想請問大大是我操作不正確還是,並不支援這項功能呢?
例如,一個欄位中內容為:[若同意購買請按下確認鍵。],那我沒辦法只把內文中的「確認」兩字取代成「確定」嗎?
希望大大能幫我解惑 >"<

一整天 提到...

Emily Shin,
請下載新版的
http://bestdaylong.com/download.php?id=12
將取代原則改成"部分取代"就可以.

mingyen li 提到...
作者已經移除這則留言。
mingyen li 提到...

您好,您的程式太好用了,請問取代範圍除了"作用中工作表",全部工作表,可否新增"指定工作表名稱"的選項嗎?麻煩您了!

蔡明和 提到...

mingyen li您好,
您要的功能已經加上,請下載試看看是否OK.

金Nero 提到...

板主您好,一直都在使用V4版,請問是否可以新增"取代結果"的功能(例如:完成取代"4"筆)?

金Nero 提到...

板主您好,一直都在使用V4版,請問是否可以新增"取代結果"的功能(例如:完成取代"4"筆)?

金Nero 提到...

板主您好,一直都在使用V4版,請問是否可以新增"取代結果"的功能(例如:完成取代"4"筆)?

grax 提到...

小弟使用的是excel 2003, 加上您的v4版本,大大增加了效率,但發現一個問題,若儲存格的字數大於255,就會type mismatch,請問是否有解呢,小弟對VBA一竅不通呀⋯⋯

蔡明和 提到...

grax您好,
我這邊試沒有您說的問題,是否方便寄有問題的excel檔案到我信箱,我來找看看是什麼問題造成的.

贊助

彰化一整天粉絲團

About Me

我的相片

很多人問我為什麼叫"一整天",其實這是有原故的.有一天看一本書.就是說如何介紹自己.裡面說到如何讓別人對你的名字有印象.就要讓別人跟你的名字或是身體的一些特徵有關.比如很胖.就會被別人取小胖或是胖子.或是跟名字有關的諧音,那天看布袋戲中有一個叫”一頁書”的角色.所以我就想我的名字.可以想出什麼字出來.結果我把我的名字"明和",拆成日月和,日月和在一起,就代表白天晚上都在一起.就取名為"一整天",而這個"一整天"跟我的名字有關也方便別人記憶,後來發現"一整天"這個名詞太常被用,所以就在"一整天"前面加上"彰化"變成"彰化一整天",為什麼要加彰化.因為我老家在彰化,所以以後別人要找我的部落格的話.我都會跟他們說,請到yahoo或是google打"彰化一整天"第一筆出現的就是我的Blog. PS:現在只要輸入"一整天"可以找到.

Google+ Followers