Google Sheets 試算表欄位中,數字以 0 開頭的話都會消失,那要怎麼保留這個0呢?

有沒有遇過這樣的情況?
在試算表裡面,不管用的是 Google Sheets 或是 Excel, Numbers,只要欄位裡面輸入的數字字串,以0開頭的時候,鍵入了 Enter,這個位於字串開頭的那個 0 就自動被去掉了!?


好比最常用的電話欄位,所有的手機都是以0這個數字起頭的,結果都變成了 918XXXXXX。


難道 0 是個什麼神秘的敏感詞嗎?


無法在試算表欄位中,輸入以0為首的數值
無法在試算表欄位中,輸入以0為首的數值

特別麻煩的是,如果是一份很大型的資料表,有些數字是以0為開頭,有些不是,那麼就不能用其他的方式來固定加上一個0。


有什麼方式可以避免或是修正這個錯誤呢(也不知道是使用者的錯誤,還是軟體的錯誤)?


不急不急,可以修正的。
其實這也不是誰的錯誤,只是資料格式判讀的問題,我們接下來就跟大家分享一下解決方法吧!


在試算表欄位中,如何讓0開頭的數字保留0


會發生這樣的問題,原因很簡單,但真的很容易被忽略。


問題的主因,其實就是欄位格式被軟體判讀為數字格式,不管是 Excel, 還是 Mac 上的 Numbers 或現在最常用的 Google Drive 裡的 Sheets 試算表,只要是純數字格式,那麼這串數字最前面的 0,一律當成沒有意義的,所以自動被軟體去除。


修正的方式,當然就是把欄位格式改為『一般文字/純文字』的格式,這樣馬上就會把 0 還給你。
理解了問題背後的邏輯,那麼我們就來看一下,實際的作法是怎麼樣的呢?


Google Sheets 試算表更改欄位格式


首先我們以 Google Sheets 試算表為範例,看看如何來更改欄位的資料格式。


在 Google Sheets 變更數值格式
在 Google Sheets 變更數值格式
  • 開啟 Google Sheets 試算表。
     
  • 先選取你要變更的那個欄位。
     
  • 接著點選上方的功能選單 > 格式(Format) > 數值(Number)。
     
  • 滑鼠移到『數值(Number)』的子項目之後,Google Sheets 會再展開一個很多數值資料格式讓你選擇。
     
  • 理論上,在『數值(Number)』的子項目裡,預設都是會勾在『自動(Automatic)』這個格式,我們只要把自動換成下面一個:『純文字(Plain Text)』。
     
  • 然後重新再輸入一次這個以0為首的數值,那個0是不是就還給你了呢!

在 MacOS 裡的 Numbers 如何更改欄位格式呢?


接下來,我們再以 MacOS 內建的試算表 Numbers 為例,一起看看如何更改欄位格式?


在 MacOS Numbers 變更數值格式
在 MacOS Numbers 變更數值格式
  • 跟之前一樣的,開啟 Numbers。
     
  • 點選你要更改的那個欄位。
     
  • 然後看向 Numbers 的側邊欄,他有幾個功能頁籤:表格、輸入格、文字、排列。
     
  • 點選側邊欄的輸入格標籤。
     
  • 接著在輸入格標籤裡,就會看到最上面有一個資料格式,下面的選單一樣預設是『自動』。
     
  • 把資料格式選單裡的自動,改選為『文字』。
     
  • 重新輸入你的數值,0就會出現了。
     
  • 收工。

單一欄位,或是整欄一起變更


之前示範的步驟,是以單一欄位為主,使用情境上比較像是一個資料表中,某一兩個欄位需要變更,可以單獨點選這幾個欄位修正。


如果是整欄都要修正的話,那麼步驟基本上都是一樣的,唯一不同的,就是不要選單一一個欄位,而是直接點選欄位最上面的那個有英文字母的那一格,點選之後,就會自動選取整欄,然後變更這一整欄的數值格式。


如何避免試算表自動偵測數值格式呢?


有朋友可能就會好奇了,那要怎麼樣來避免試算表軟體自動偵測我們的資料格式呢?


因為很多種數字類型的資料,其實都不該屬於數字,像是之前提到的行動電話號碼,或是會員編號、訂單編號(或任何編號)等等。


之前的修正方式,對於一筆一筆資料輸入時,很有幫助,倘若是匯入的話,一匯入,那麼這個 0 就先被消滅了,就算改了資料格式,還是要一個一個加回來,超麻煩的呀!


真的超麻煩,我也幹過這樣的事,當真是一面做,一面在心裡面複習這輩子學過的所有的髒話...


不過還是有一些補救方案可以試試。
接下來我們就再來聊一聊,如果是匯入的資料,怎麼避開試算表的自動數值偵測呢?


合併欄位法


首先,我們可以用合併欄位的方式,這個方式很適合整欄的數值都固定是以 0 為首的,像是手機號碼的欄位。
也就是說,不管任何條件,反正通通固定前面加上0這樣。


作法就是在手機欄位上,『向右插入 1 欄』,然後在第一個欄位裡打入公式:=CONCATENATE("0",手機欄位)。
如果手機欄位是 B1,那麼這個合併欄位裡的公式,就是:

=CONCATENATE("0",B1)

接著把這個合併欄位向下填滿,這樣就會得到一個有0為首的手機號碼資料欄了。
喔!別忘了要把原來的手機號碼那個資料欄給隱藏起來喔。


匯入前,修改原始資料


第二個方式,就是直接從原始資料來下手。


邏輯也很簡單,就是把那個資料欄多加上一個固定的非數字的符號,例如固定最前面或最後面加一個斜線(/)、星號(*),或是任何一個字母也可以,但不要用加減號喔(+, -),這兩個符號也是會被當成純數字處理的。


這樣試算表就不會預設把這個欄位當成『純數字』,而預設就會以文字字串來處理。


匯入後,再一併取代掉那個欄位裡固定的那個添加的符號,這樣就可以了。