2022/08/27

使用SUBSTITUTE / 取代功能將文字間的空白格移除

      , , ,      No comments   

有的時候，我們拿到的資料不一定長得很整齊，有可能含有亂七八糟的空白格，甚至是含有符號，而且它們出現的位置還不一致 (參考下方範例圖的  綠底  資料) 。如果你必須要整理那些不整齊的資料，一一核對檢查需要花上不少時間，如果想要快速解決，就可以使用取代功能，或是使用函數SUBSTITUTE，兩個都很簡單，看自己需求則一使用就可以啦。

    基本說明

    SUBSTITUTE 函數公式，在 Microsoft Excel 和 Google Sheet 中皆可使用，且用法相同，此篇使用 Google Sheet 作為範例圖。取代功能也是兩者皆可使用，且用法相同，僅畫面和敘述有一點差異，在下方會分開說明。
    為方便辨識，儲存格標示為  黃底  的是帶有函式資料的儲存格；儲存格標示為  綠底  的為可修改資料，且會影響公式結果的儲存格；標示為  淺灰底  的為黃底儲存格的公式文字。



    取代功能實際操作 --- Microsoft Excel


    1. 選擇要取代的資料範圍，範例中直接選擇A欄。



    2. 叫出取代功能 (兩個方式2選1)
      ● 輸入快捷鍵：Ctrl + H
      ● 選單列：常用 → 編輯 → 尋找與選取 → 取代



    3. 取代功能設定
      ● 尋找目標：輸入要被取代的文字或符號。
      範例中因為有許多空格讓文字變的很凌亂，因此尋找目標輸入一個空格 (點擊空白鍵Space一下)。
      ● 取代成：輸入要轉換的文字或符號。
      範例中要將空白格移除，因此不需要輸入任何文字或符號，讓取代的文字為空白即可。
      ● 搜尋選項：本範例不需要調整搜尋選項。
      補充 - 搜尋範圍可選擇工作表 (僅所在的工作簿會被調整)，也可以調整為活頁簿 (整份檔案中包含的工作表都會被調整)。下方還有2個核取方塊可以調整是否要大小寫相符、內容是否完全相符。



    4. 點擊「全部取代」，取代視窗中會告知共取代了幾筆資料。
      範例中共取代了9筆資料。



    5. 完成，將取代視窗關閉即可。




    取代功能實際操作 --- Google Sheet


    1. 選擇要取代的資料範圍，範例中直接選擇A欄。



    2. 叫出取代功能 (兩個方式2選1)
      ● 輸入快捷鍵：Ctrl + H
      ● 選單列：編輯 → 尋找與取代

    3. 取代功能設定
      ● 尋找：輸入要被取代的文字或符號。
      範例中因為有許多空格讓文字變的很凌亂，因此輸入一個空格 (點擊空白鍵Space一下)。
      ● 取代為：輸入要轉換的文字或符號。
      範例中要將空白格移除，因此不需要輸入任何文字或符號，讓取代的文字為空白即可。
      ● 搜尋：本範例不需要調整搜尋選項。
      補充 - 搜尋範圍可選擇這份工作表 (僅所在的工作簿會被調整)，也可以調整所有工作表 (整份檔案中包含的工作表都會被調整)。下方還有5個核取方塊可以調整，前2個與Microsoft Excel相同，為大小寫是否要相符、內容是否要完全相符。後面3個比較特別，使用規則運算式搜尋(較為複雜可能之後再補充)；同時在公式中搜尋，可以用來搜尋公式，也可以直接一次修改公式中的內容，勾選後所選擇的儲存格若帶有公式，會轉換為顯示公式而非結果；一併在連結中搜尋，此選項經測式僅能用來搜尋連結，不能進行取代。


    4. 點擊「全部取代」，取代視窗中會告知共取代了幾個文字數量。
      範例中共取代了25個空白格。


    5. 完成，將取代視窗關閉即可。




    函數SUBSTITUTE

    使用新的資料取代儲存格中現有的資料。
    函數公式：  SUBSTITUTE( 要取代的位置, 要取代的資料 , 要替換的內容 )  

    使用方式：
    • 要取代的位置
    1. 選用儲存格內容
      例如：      SUBSTITUTE( A2 , 要取代的資料 , 要替換的內容 )  
    2. 文字直接寫在公式中
      例如：SUBSTITUTE( "SuperSmartCookie" , 要取代的資料 , 要替換的內容 )  
    3. 數值直接寫在公式中
      例如：SUBSTITUTE( 123456789 , 要取代的資料 , 要替換的內容 )  
    • 要取代的資料
    1. 選用儲存格內容
      例如：SUBSTITUTE( 要取代的位置 ,  D3 , 要替換的內容 )  
    2. 文字直接寫在公式中
      例如：SUBSTITUTE( 要取代的位置 , "SuperSmartCookie" , 要替換的內容 ) 
    3. 數值直接寫在公式中
      例如：SUBSTITUTE( 要取代的位置 , 123456 , 要替換的內容 ) 
    • 要替換的內容
    1. 選用儲存格內容
      例如：SUBSTITUTE( 要取代的位置, 要取代的資料 , E5 )  
    2. 文字直接寫在公式中
      例如：SUBSTITUTE( 要取代的位置, 要取代的資料 , "SuperSmartCookie" ) 
    3. 數值直接寫在公式中
      例如：SUBSTITUTE( 要取代的位置, 要取代的資料 , 123456 )



    函數實際操作

    此篇的函數公式結果顯示如下(  黃底  內容)：
      《 步驟 》
    1. 使用函數 SUBSTITUTE 將空白格取代為空白：
      在儲存格 D3 加入函數，要調整的內容儲存格為 B3，空白格、空白皆為文字，需要使用「"」雙引號框起。
        =SUBSTITUTE(B3, " " , "")  
    2. 將公式代入其他儲存格：
      選擇已經填寫好公式的儲存格，滑鼠左鍵點擊儲存格右下角不放，往下拉到需要代入公式的儲存格，放開左鍵。

    3. 完成~~~~~

    EXCEL公式小工具

    使用方式
    1. 輸入欄中填入儲存格位置，例如：A8、C12、H3... ...
    2. 輸入要取代的文字或符號，若想取代空白格直接輸入空白格一次
    3. 輸入要替換的文字或符號，若不需要可留空
    4. 按下 Enter 按鈕
    5. 點擊輸出的函數公式就可以直接複製
    6. 將它貼在想要顯示的儲存格內即可
    要取代的位置 
    取代文字/符號
    替換文字/符號(選填)



    Copied!

    0 Comments:

    張貼留言