如何將試算表裡面的換行和TAB等特殊字元換成空格？ / How to Replace Special Characters (Line Breaks and Horizontal Tabs) from Cells by Spaces in Spreadsheets?

7月 19, 2019 Software/GoogleDoc , Software/LibreOffice 4 Comments Edit Copy Download

我們時常使用LibreOffice Calc、Google試算表、以及Microsoft Excel等試算表工具來整理資料，但當資料裡面有些「換行」(new lines)、「Tab」(也就是製表鍵(tabulator key)或表格鍵(tabular key)的縮寫)、前後空白、中文空白時，常常會在轉換成CSV、輸入到Weka或其他工具時，造成解讀資料時發生錯誤。

要移除「換行」和「Tab」等特殊字元的話，難道還要寫Python程式嗎？不用，我們只要在試算表工具裡面加入一個函數，就可以輕鬆將「換行」和「Tab」等特殊字元轉換成空白字元囉。讓我們來看看怎麼做吧。

含有特殊字元的資料 / Special characters in cells

• Google試算表 (建立副本)
• OpenDocument Spreadsheet
• Microsoft Excel
  

讓我們以這個試算表資料為例。在這個試算表中，A2儲存格(cell)裡面包含了前面空白、換行、中文的全形空格等特殊字元，內容如下：

 LINE1
LINE2 　
LINE3

A3儲存格裡面包含了前後空白、中間有「Tab」等特殊字元，內容如下：

 COL1 COL2 

綜合以上兩個儲存格，我們要移除的特殊字元有下列四種：

• 前後空白
• 換行
• 中文的全形空白「　」
• Tab

我們要如何用試算表內的函數來將這些特殊字元取代成為空白字元呢？讓我們繼續看下去吧。

移除特殊字元的函數 / Function to remove Special characters

我參考了Alexander Frolov的函數，整理出可以將這些特殊字元取代為空白字元的函數如下：

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," "),CHAR(9)," "),"　"," "))

裡面最重要的是記得把紅字的「A2」替換成有特殊字元的儲存格位置。這樣就可以把A2儲存格轉換成以下結果：

LINE1 LINE 2 LINE3

而A3儲存格也可以轉換成以下結果：

COL1 COL2

你可以注意到，儲存格內前後的空白字元、換行、Tab、以及中文的全形空白，全部被替換成空格了。這樣我們就完成將特殊字元取代為空白字元的工作了。

這個函數可以用於LibreOffice Calc、Google試算表以及Microsoft Excel等各種試算表工具中，請放心使用吧。

函數說明 / Functions

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," "),CHAR(9)," "),"　"," "))

上述這個一個很長的函式中用到了許多函數，讓我們參考Google試算表函式說明，一個一個來看看：

• TRIM() 移除文字開頭、結尾和重複的空格。
• SUBSTITUTE() 以新文字取代字串中的現有文字。
• CHAR() 根據目前的 Unicode 編碼表將數字轉換成字元。在此函數中我用了三種不同的CHAR。CHAR(9)表示Tab、CHAR(10)表示Line Feed (LF)的換行、CHAR(13)表示Carriage return (CR)的換行。關於數字與字元的對照，可以參考ASCII code這張表。

結語 / In closing

在準備使用Weka來做文字探勘之前，我們通常使用這些試算表工具來保存、整理資料，並將資料另存為CSV檔案後，再丟到Weka中分析。但如果沒有移除「換行」、「Tab」這些特殊字元，Weka在開啟時就會遭遇錯誤。

以往我都會在使用爬蟲抓取資料時，就在程式中直接移除這些特殊字元。但這次我使用來自別人資料庫匯出的試算表檔案，那特殊字元的移除就得自己來。

網路上也有文章在介紹如何移除換行。一部分人使用CLEAN()函數，但它會直接移除換行、Tab等特殊字元，不會讓它變成空白。也就是說，使用CLEAN()處理上述例子中的A3儲存格的話，最後輸出的結果會變成「COL1COL2」，這樣也會讓我們誤以為兩行文字是同一個詞彙，這會導致斷詞處理時發生誤判。

另一部分有人主張使用尋找和取代對話視窗來操作，例如「Excel-儲存格中的斷行與去除斷行」，但每個試算表工具的尋找及取代作法都不同，而且每次都要手動操作，也不符合我的需求。

最後還是整理一個函數，能夠快速套用到各種試算表工具中，這才是我要的最佳作法。在這邊分享給各種不想寫程式、又需要移除特殊字元的讀者們。

閒聊 / Off-topic talk

嗯，好啦，又讓我們來聊聊Blogger Editor。這篇應該用比較少時間來寫了，但我覺得時間計算上一定那裡有問題，我印象中應該是沒有花3個多小時來寫這篇......吧？

為了搞清楚我有沒有計算錯誤，我把自己寫文章的行為用Google分析的行為事件追蹤功能記錄起來，不過不知為何，Google分析的報表似乎是沒有完整列出我做的所有行為，而且我傳送的時間戳記也有錯誤。真是尷尬，發現了更多Bug。

除了加入了用Google分析記錄寫作行為之外，我這篇還將圖片識別文字的OCR工具Tesseract.js加入到Blogger Editor中。原本我插入圖片時，圖片的檔案名稱只會有插入的日期與時間，例如：

• 2019-0718-111337.png

接著我使用Tesseract.js把圖片裡面的文字抽取出來，找出兩字以上的單詞，取出數個單詞加入到檔名中，就變成了如下檔名：

• 2019-0718-111337-Find-and-replace-Find-Search-All.png

這樣是不是比原本的檔名更有可讀性了呢？此外，我也把所有識別出來的文字加入到圖片標籤<img>的alt屬性中，作為圖片不能讀取時才會顯示的替代文字。例如上圖的alt裡面就是：

Find and replace x
Find \n
」一
Search All sheets +

8 Match case

[J Match entire cell contents

[8 search using regular expressions Help

[J Also search within formulas

Find Replace Replace all區曙

OCR辨識的結果不能說完美，但至少可以從裡面找出一些詞彙。這樣做的主要目的是為了提升搜尋引擎最佳化(Search Engine Optimization, SEO)，而且呈現替代文字alt，也會對視障人士來說比較友善。視障人士在網頁上遇到圖片時，通常會使用螢幕器朗讀器或是點字顯示器來讀取圖片相關的文字，以此瞭解圖片的內容。製作對視障人士友善的無障礙網頁，也是我們應該努力的方向。有興趣的朋友可以更進一步看看「知識小百科:視障人士怎樣上網？」。

在撰寫開始之前，Blogger Editor的Issues只有8項，寫完這篇之後就暴增到16項了。就這樣不斷在改程式、寫文章之間，再繼續努力吧。

那這次對在試算表工具中移除換行、Tab等特殊字元的函數說明就寫到這裡了。最後我有些問題想問問大家：

• 你最常用那一種試算表工具呢？LibreOffice Calc？Google試算表？還是Microsoft Excel？
• 你有用過上述處理文字的函數嗎？特別是TRIM()和SUBSTITUTE()。
• 你以往想要移除換行或Tab時，你都是怎麼處理的呢？

歡迎在下面的留言處跟我們分享你的想法。大家的意見是我繼續分享的動力喔！如果你覺得我這篇教學還算有用的話，請幫我在AddThis分享工具按讚、將這篇分享到Facebook等社群媒體吧！

感謝你的耐心閱讀，我是布丁，讓我們下一篇見。