iT邦幫忙

0

VBA模糊刪除指定區間

vba
工程詩 2021-07-28 16:56:44 ‧ 993 瀏覽

  • 分享至 

  • xImage

我有一個字串如下

p.msonormal0, li.msonormal0, div.msonormal0
	{mso-style-name:msonormal;
	mso-style-unhide:no;
	mso-margin-top-alt:auto;
	margin-right:0in;
	mso-margin-bottom-alt:auto;
	margin-left:0in;
	mso-pagination:widow-orphan;
	font-size:12.0pt;
	font-family:SimSun;
	mso-bidi-font-family:Calibri;}
span.wordsection1Char
	{mso-style-name:"wordsection1 Char";
	mso-style-priority:99;
	mso-style-unhide:no;
	mso-style-locked:yes;
	mso-style-link:wordsection1;
	font-family:"MS Mincho";
	mso-ascii-font-family:"MS Mincho";
	mso-fareast-font-family:"MS Mincho";
	mso-hansi-font-family:"MS Mincho";
	color:#000099;
	mso-fareast-language:JA;}
p.wordsection1, li.wordsection1, div.wordsection1
	{mso-style-name:wordsection1;
	mso-style-priority:99;
	mso-style-unhide:no;
	mso-style-link:"wordsection1 Char";
	mso-margin-top-alt:auto;
	margin-right:0in;
	mso-margin-bottom-alt:auto;
	margin-left:0in;
	mso-pagination:widow-orphan;
	font-size:10.0pt;
	font-family:"MS Mincho";
	mso-bidi-font-family:Calibri;
	color:#000099;
	mso-fareast-language:JA;}
span.EmailStyle20
	{mso-style-type:personal;
	mso-style-noshow:yes;
	mso-style-unhide:no;
	font-family:"Arial Unicode MS",serif;
	mso-ascii-font-family:"Arial Unicode MS";
	mso-hansi-font-family:"Arial Unicode MS";
	color:black;}

我想要使用VBA刪除

span.wordsection1Char
	{mso-style-name:"wordsection1 Char";
	mso-style-priority:99;
	mso-style-unhide:no;
	mso-style-locked:yes;
	mso-style-link:wordsection1;
	font-family:"MS Mincho";
	mso-ascii-font-family:"MS Mincho";
	mso-fareast-font-family:"MS Mincho";
	mso-hansi-font-family:"MS Mincho";
	color:#000099;
	mso-fareast-language:JA;}
p.wordsection1, li.wordsection1, div.wordsection1
	{mso-style-name:wordsection1;
	mso-style-priority:99;
	mso-style-unhide:no;
	mso-style-link:"wordsection1 Char";
	mso-margin-top-alt:auto;
	margin-right:0in;
	mso-margin-bottom-alt:auto;
	margin-left:0in;
	mso-pagination:widow-orphan;
	font-size:10.0pt;
	font-family:"MS Mincho";
	mso-bidi-font-family:Calibri;
	color:#000099;
	mso-fareast-language:JA;}

得到以下結果

span.EmailStyle20
	{mso-style-type:personal;
	mso-style-noshow:yes;
	mso-style-unhide:no;
	font-family:"Arial Unicode MS",serif;
	mso-ascii-font-family:"Arial Unicode MS";
	mso-hansi-font-family:"Arial Unicode MS";
	color:black;}

我嘗試使用Replace，但那隻對明確、單行有效果...

備註 : span.wordsection1Char之間屬性、值不一定

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

1
japhenchen
iT邦超人 1 級 ‧ 2021-07-29 16:03:56
最佳解答

用VBA跑正則表達式

' 建立正規表示法變數
Dim regEx As New RegExp

' 要匹配與取代的文字資料
Dim oldString As String
oldString = "p.msonormal0, li.msonormal0, div.msonormal0
	{mso-style-name:msonormal;
	mso-style-unhide:no;
	mso-margin-top-alt:auto;
	margin-right:0in;
	mso-margin-bottom-alt:auto;
	margin-left:0in;
	mso-pagination:widow-orphan;
	font-size:12.0pt;
	font-family:SimSun;
	mso-bidi-font-family:Calibri;}
span.wordsection1Char
	{mso-style-name:"wordsection1 Char";
	mso-style-priority:99;
	mso-style-unhide:no;
	mso-style-locked:yes;
	mso-style-link:wordsection1;
	font-family:"MS Mincho";
	mso-ascii-font-family:"MS Mincho";
	mso-fareast-font-family:"MS Mincho";
	mso-hansi-font-family:"MS Mincho";
	color:#000099;
	mso-fareast-language:JA;}
p.wordsection1, li.wordsection1, div.wordsection1
	{mso-style-name:wordsection1;
	mso-style-priority:99;
	mso-style-unhide:no;
	mso-style-link:"wordsection1 Char";
	mso-margin-top-alt:auto;
	margin-right:0in;
	mso-margin-bottom-alt:auto;
	margin-left:0in;
	mso-pagination:widow-orphan;
	font-size:10.0pt;
	font-family:"MS Mincho";
	mso-bidi-font-family:Calibri;
	color:#000099;
	mso-fareast-language:JA;}
span.EmailStyle20
	{mso-style-type:personal;
	mso-style-noshow:yes;
	mso-style-unhide:no;
	font-family:"Arial Unicode MS",serif;
	mso-ascii-font-family:"Arial Unicode MS";
	mso-hansi-font-family:"Arial Unicode MS";
	color:black;}"

' 設定規則，以【不含】emailstyle字樣(不分大小寫)的做替換
regEx.Pattern = "^(?:(?!\bemailstyle).)*\n(?:\s*\n*\r*)(?:\n?\r?)\{(?:[^}]*\r?\n?)\}"

' 替代的文字，把符合上列規則的換成空白(跟刪除一樣意思)
Dim MyReplace As String
MyReplace = "" 

' 設定成不分大小寫
regEx.IgnoreCase = True
' 設定全域選項
regEx.Global = True
' 多行
regEx.Multiline = True

' 使用正規表示法匹配並取代文字
If regEx.Test(oldString) Then
  Result = regEx.Replace(oldString, MyReplace)
  MsgBox ("取代後的結果：" & Result)
Else
  MsgBox ("匹配失敗")
End If

(終於搞懂必不包含特定字串的用法，功力又提升一甲子...秒)

重點是這一行規則，很燒腦啊

regEx.Pattern = "^(?:(?!\bemailstyle).)*\n(?:\s*\n*\r*)(?:\n?\r?)\{(?:[^}]*\r?\n?)\}"
japhenchen iT邦超人 1 級 ‧ 2021-07-29 16:06:15 檢舉

其實犯不著跑VBA，用Code直接按CTRL+H執行正則代換，一樣可以做完這件事

工程詩 iT邦新手 4 級 ‧ 2021-07-30 08:27:44 檢舉

感謝，這個腳本太厲害了，我想破頭都想不出來..

登入發表回應
1
海綿寶寶
iT邦大神 1 級 ‧ 2021-07-28 17:13:18

試試看
result = Mid(source, InStr(1, source, "span.EmailStyle20"))

工程詩 iT邦新手 4 級 ‧ 2021-07-30 08:27:12 檢舉

感謝您，可以刪除，但裡面內容無法刪除

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22203 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙