Cách thêm các số 0 vào đầu các giá trị trong Excel

Trong bài hướng dẫn lần này, Học Excel Online sẽ hướng dẫn các bạn các giữ nguyên các số 0 ở đầu chuỗi văn bản trong Excel, cách hiển thị, loại bỏ hay ẩn các số 0 đó.
Nếu bạn sử dụng Excel không chỉ để phục vụ cho tính toán mà còn để lưu các bản ghi như mã zip, số bảo mật, ID của nhân viên, chắc chắn bạn sẽ cần phải giữ lại các con số 0 đứng đầu các ô. Tuy nhiên, nếu bạn nhập một mã zip như “00123” trong một ô thì Excel sẽ tự động cắt thành “123”.
Vấn đề này xảy ra do Microsoft Excel xử lý mã bưu điện, số điện thoại và các mục tương tự khác giống như cách xử lý số, và tự động áp dụng định dạng tổng quát như đối với các số đó là tự xóa đi các số 0 đứng ở đầu. May mắn là Excel cũng cung cấp cho chúng ta phương pháp để có thể giữ lại những số 0 đứng ở đầu các ô. Trong bài viết lần này, Học Excel Online sẽ hướng dẫn các bạn phương pháp đó.

Cách giữ lại những số không đứng đầu khi nhập vào Excel

Có một cách đơn thuần để những người mới khởi đầu làm quen với Excel hoàn toàn có thể vận dụng để giữ lại những số 0 đứng đầu trong một ô, ví dụ như 01. Để triển khai điều này, tất cả chúng ta chỉ cần biến hóa định dạng ô thành Text :

Cách 1: 

• Chọn 1 hoặc nhiều ô mà bạn muốn nhập số với số đầu tiên là 0

  Bạn đang đọc: Cách thêm các số 0 vào đầu các giá trị trong Excel

• Tới phần Trang chủ (Home tab) > Number group, rồi chọn Text.

Ngay khi bạn nhập một số bắt đầu bằng số 0, Excel sẽ hiển thị một hình tam giác nhỏ màu xanh lá ở góc trên cùng bên trái của ô thể hiện điều này không hợp lệ. Để loại bỏ tam giác đó, bạn chọn một hoặc nhiều ô cần quan tâm rồi nhấp vào dấu hiệu cảnh báo, sau đó chọn Ignore Error.

Các bạn xem thêm hình bên dưới để thấy hiệu quả

Cách 2: 

Một cách khác để giữ được những số 0 đứng ở đầu trong Excel là đặt ở trước nó một dấu nháy ( ‘ ). Ví dụ như, thay vì gõ 01, bạn gõ là ’ 01, như vậy bạn sẽ không cần đổi khác định dạng của ô đó .
Lưu ý : Cách làm này có chút số lượng giới hạn đó là hiệu quả của chuỗi văn bản đó sẽ không phải là số, do đó không hề sử dụng để làm phép tính và không vận dụng được những công thức số. Nếu bạn không muốn xảy ra thực trạng đó thì bạn hoàn toàn có thể đổi khác những bộc lộ giá trị trực quan bằng cách vận dụng định dạng số tùy chỉnh như ví dụ minh họa tiếp sau đây .

Cách hiển thị số không đứng đầu trong Excel bằng sử dụng định dạng số tùy chỉnh

Để hiển thị số 0 đứng đầu, những bạn hoàn toàn có thể vận dụng định dạng số tùy chỉnh bằng cách triển khai những bước sau :

1. Chọn một hoặc nhiều ô bạn cần hiển thị số 0 ở đâu và nhấn ctrl + 1 để mở hộp thoại Format Cells.

2. Trong phần Category, chọn Custom.

3. Nhập mã định dạng vào phần Type.
   Trong nhiều trường hợp, bạn sẽ cần một mã định dạng với ký tự 0, ví dụ như 00. Số số 0 trong mã định dạng tương ứng với tổng số chữ số mà bạn muốn hiển thị trong một ô (các bạn có thể theo dõi ví dụ dưới đây).

4. Nhấp vào OK để lưu thay đổi.

Ví dụ: để thêm số 0 ở đầu nhằm tạo một số có 5 chữ số, bạn sử dụng mã định dạng 00000

Từ việc sử dụng các định dạng số tùy chỉnh của Excel, bạn có thể thêm các số 0 vào đầu để tạo các số có độ dài cố định, giống như ví dụ bên trên, và cả các số có độ dài không cố định. Tất cả các đều được thực hiện nhờ việc sử dụng mã định dạng:
0 – hiển thị thêm số không
# – không hiển thị số 0 thêm vào
Ví dụ, nếu bạn áp dụng định dạng #000 với một số ô, dù bạn nhập vào số nào thì số đó sẽ có thêm 3 số không đứng ở đầu.
Các định dạng số tùy chỉnh của bạn cũng có thể bao gồm cả dấu cách, dấu gạch ngang, dấu ngoặc đơn,….
Bảng tính sau đây sẽ cho bạn thấy ví dụ về các định dạng tùy chỉnh có thể hiển thị các số 0 đứng đầu trong Excel.

Mẹo: Excel có một vài định dạng được đặt sẵn cho các mã bưu điện, số điện thoại và số an sinh xã hội, thể hiện qua hình bên dưới:

Lưu ý: Phương pháp này tốt nhất là sử dụng trong những trường hợp bạn làm việc với một tập hợp dữ liệu số và kết quả là các con số chứ không phải văn bản. Cách này chỉ làm thay đổi hiển thị của một số nhưng không phải thay đổi hẳn số đó: dù có thêm các số 0 hiển thị ở trong các ô nhưng giá trị thực sự của số ban đầu thì vẫn giữ nguyên. Khi áp dụng tính toán các ô chứa những số đó thì phép tính sẽ thực hiện với giá trị ban đầu. Các định dạng tùy chỉnh có thể áp dụng được cho dữ liệu số (gồm các con số và ngày tháng) và kết quả cho ra cũng sẽ là các con số hoặc ngày tháng.

Cách thêm số 0 vào đầu trong Excel sử dụng hàm TEXT

Định dạng số tùy chỉnh cho phép hiển thị số 0 ở phía trước của một số mà không làm thay đổi giá trị cơ bản của số đó, còn hàm Excel TEXT thì thêm số 0 vào số ban đầu theo dạng làm thay đổi cả bản chất của số đó.
Để thêm các số không vào đầu một số sử dụng công thức TEXT(value, format_text), bạn sử dụng các mã định dạng giống như ở các định dạng số tùy chỉnh. Tuy nhiên, kết quả của hàm TEXT luôn luôn là một chuỗi văn bản, ngay cả khi trông hình thức của nó giống như một số.
Ví dụ, để chèn số 0 vào trước một giá trị trong ô A2, ta sử dụng công thức:
= TEXT (A2, “0 #”)
Để tạo chuỗi số 0 đứng phía trước với độ dài cố định là 5 ký tự thì ta sử dụng chuỗi sau:
= TEXT (A2, “000000”)
Bạn cũng cần lưu ý rằng hàm TEXT cần phải có mã định dạng nằm kèm trong dấu ngoặc kép và kết quả nhận được như sau:

Lưu ý: Hàm Excel TEXT luôn trả về một chuỗi văn bản chứ không phải số, do đó bạn không thể dùng trong các kết quả tính toán số học và các công thức khác, mà chỉ dùng để so sánh kết quả đầu ra với các chuỗi văn bản khác.

Cách thêm số 0 vào đầu chuỗi văn bản

Trong các ví dụ trước, các bạn đã thấy cách thêm số 0 vào trước một số trong Excel. Vậy nếu bạn muốn thêm số 0 vào trước một chuỗi văn bản, ví dụ như 0A102 thì sao? Trong trường hợp đó, cả hàm TEXT lẫn định dạng tùy chỉnh đều sẽ không có tác dụng vì chúng chỉ có thể xử lý được các giá trị số.
Nếu giá trị cần thêm 0 vào có chứa các ký tự chữ hoặc văn bản thì các bạn có thể áp dụng một trong những cách sau, những cách này là giải pháp phổ quát cho cả số và chuỗi văn bản.

Công thức 1 : Thêm những số 0 vào đầu sử dụng hàm RIGHT

Cách đơn giản nhất để đặt số 0 lên đầu trước các chuỗi văn bản trong Excel chính là sử dụng hàm RIGHT:
RIGHT("0000" & cell, string_length)
Trong đó:
“0000” là số số không tối đa mà bạn muốn thêm vào. Ví dụ: để thêm vào 2 số 0 thì bạn nhập “00”.
Cell là một ô tham chiếu đến ô có chứa giá trị ban đầu.
String_length là số ký tự mà chuỗi kết quả phải chứa.

Ví dụ: để tạo chuỗi ký tự gồm 6 ký tự không có tiền tố dựa trên giá trị trong ô A2 thì bạn sử dụng công thức:
= RIGHT (“000000” & A2, 6)
Cách hoạt động của công thức này là thêm vào 6 con số không vào giá trị chứa trong ô A2 (“000000″&A2), sau đó lấy ra đúng 6 ký tự. Kết quả là nó chỉ chèn đúng số số không đủ để đạt đến giới hạn tổng số ký tự trong chuỗi.

Trong ví dụ trên, số số không tối đa bằng tổng chiều dài của chuỗi ký tự (6 ký tự), vì thế tất cả các chuỗi kết quả đều có chiều dài là 6 ký tự (chiều dài cố định). Nếu áp dụng cho một ô trống thì công thức sẽ trả về một chuỗi gồm 6 số không.
Tuy thuộc vào mục đích sử dụng, bạn có thể chèn thêm số số không khác nhau và thay đổi tổng số ký tự theo ý muốn, ví dụ:

=RIGHT(“00″&A2, 6)

Kết quả nhận được là chuỗi có chiều dài biến hóa, hoàn toàn có thể chứa tối đa 2 số không ở đầu :

Công thức 2 : Thêm số 0 vào đầu bằng cách sử dụng hàm REPT và LEN

Một cách khác để chèn số 0 vào trước một chuỗi văn bản trong Excel là sử dụng phối hợp hàm REPT và hàm LEN :

REPT(0, number of zeros-LEN(cell))&cell

Ví dụ, để thêm các số 0 vào một giá trị trong ô A2 để tạo chuỗi gồm 6 ký tự, bạn sử dụng công thức:
=REPT(0, 6-LEN(A2))&A2

Cách hoạt động giải trí của công thức này như sau :

Hàm REPT lặp lại một ký tự cho trước 1 số ít lần nhất định và LEN trả về tổng chiều dài của chuỗi ký tự, nên ta có công thức dễ hiểu theo logic :

LEN(A2) nhận tổng số ký tự trong ô A2
REPT(0, 6-LEN(A2)) thêm vào số số không cần thiết. Để tính xem cần thêm vào bao nhiêu số 0, bạn lấy số số không cực đại trừ đi chiều dài của chuỗi trong ô A2. Cuối cùng, ta nhận được kết quả:

Lưu ý: Công thức này có thể thêm các số không vào đầu chuỗi văn bản hoặc số nhưng kết quả lại luôn là văn bản chứ không phải số.

Cách thêm một số lượng số không cố định và thắt chặt vào đầu giá trị

Để thêm tiền tố vào tất cả các giá trị trong cột (có thể là số hoặc chuỗi văn bản) với số lượng số không cố định sẵn, bạn sử dụng hàm CONCATENATE hoặc hàm toán tử.
Ví dụ, để đặt 0 trước một số trong ô A2, sử dụng những công thức sau:

=CONCATENATE(0,A2)

hoặc

=0&A2

Như những gì hiển thị trong ảnh chụp màn hình hiển thị bên dưới, công thức thêm chỉ 1 số ít 0 vào đầu hàng cho toàn bộ những ô trong một cột dù bắt đầu những ô đó chứa bao nhiêu ký tự .

Tương tự, bạn hoàn toàn có thể chèn thêm 2 số không ( 00 ), 3 số không ( 000 ) hoặc nhiều số không trước những số hoặc chuỗi văn bản .

Lưu ý: Kết của của công thức này cũng là chuỗi văn bản kể cả khi giá trị ban đầu được thêm vào là số.

Cách vô hiệu số không đứng đầu trong Excel

Phương pháp bạn sử dụng để xóa những số không đứng đầu trong Excel phụ thuộc vào vào cách mà những số không đó được thêm vào :

– Nếu trước đó, các số không được thêm vào bằng cách dùng định dạng số tùy chỉnh (số không có thể nhìn thấy được trong một ô, nhưng không hiển thị trên thanh công thức) thì bạn áp dụng một định dạng tùy chỉnh khác hoặc đặt lại định dạng General như bên dưới.
– Nếu các số không được nhập vào ô được định dạng ở mục Text (xuất hiện tam giác nhỏ màu xanh lá cây ở góc trên cùng bên trái của ô), bạn chuyển văn bản đó sang dạng số.
– Nếu các số không được thêm vào bằng cách sử dụng công thức (công thức xuất hiện trong thanh công thức khi nhấn chọn ô đó), bạn sử dụng hàm VALUE để loại bỏ chúng.

Các bạn xem hình bên dưới để hiểu hơn về 3 trường hợp trên và lựa chọn cách làm tương thích .

Loại bỏ những số không đứng đầu bằng cách biến hóa định dạng ô

Nếu những số không đứng đầu được hiển thị trong những ô có định dạng tùy chỉnh, bạn hãy biến hóa định dạng ô, đặt lại thành mặc định chung hoặc vận dụng một định dạng khác không để số không hiển thị ở đầu giá trị ô .

Loại bỏ những số không đứng đầu bằng cách chuyển sang dạng số

Khi những số không Open ở đầu giá trị trong một ô đã được định dạng ở dạng văn bản thì cách dễ nhất để vô hiệu chúng là nhấn vào một hoặc nhiều ô rồi chọn điểm có dấu chấm than, chọn tiếp Convert to Number :

Loại bỏ những số không đứng đầu bằng cách sử dụng công thức

Trong trường hợp một hoặc nhiều số không được thêm vào phía trước giá trị bằng cách sử dụng công thức thì bạn cần phải sử dụng một công thức khác để vô hiệu chúng. Công thức để vô hiệu khá đơn thuần :

=VALUE(A2)

Trong đó A2 là ô mà bạn muốn vô hiệu những số không đứng ở đầu .
Phương pháp này cũng hoàn toàn có thể được dùng để vô hiệu những số không được gõ trực tiếp vào ô ( như ở ví dụ trước ) hoặc nhập vào Excel từ một số ít nguồn bên ngoài. Nói chung, nếu bạn muốn thao tác với chuỗi những số không đứng ở đầu thì hoàn toàn có thể sử dụng hàm VALUE để chuyển văn bản sang số và vô hiệu những số không đó .
Bạn hoàn toàn có thể theo dõi hai công thức ở ảnh chụp màn hình hiển thị bên dưới :

Công thức Text ở ô B2 để thêm số không vào đầu giá trị
Công thức VALUE ở ô C2 để loại bỏ các số không đã thêm vào.

Cách ẩn những số không trong Excel

Nếu bạn không muốn hiển thị những giá trị không trong bảng tính Excel thì có hai lựa chọn như sau :

1. Để ẩn những số không trên hàng loạt trang tính, bạn chọn File > Options > Advanced, kéo xuống dưới rồi bỏ chọn mục Show a zero in cells that have zero value .

   

2. Để ẩn những giá trị không trong những ô nhất định, bạn vận dụng định dạng số tùy chỉnh cho những ô đó : # ; # ; ; @
   Để thực thi điều này, chọn những ô bạn muốn ẩn số không, nhấn Ctrl + 1 để mở hộp thoại Format Cells, chọn Custom trong Category, gõ mã định dạng ở trên vào hộp Type .

Trong ảnh chụp màn hình bên dưới đây, có thể thấy ô B2 có giá trị bằng 0 nhưng nó không được hiển thị trong ô.

Trên đây là bài viết về cách thêm, vô hiệu và ẩn những số không trong Excel. Cảm ơn những bạn đã theo dõi bài viết .
Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào việc làm như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table …
Toàn bộ những kiến thức và kỹ năng này những bạn đều hoàn toàn có thể học được trong khóa học Excel từ cơ bản tới chuyên viên tại Học Excel Online

Source: https://dvn.com.vn
Category: Hỏi Đáp