Trong bài hướng dẫn này, Học Excel Online sẽ chỉ dẫn các bạn cách hiển thị, loại bỏ hay che giấu các số 0 và bảo toàn các số 0 ở đầu chuỗi văn bản trong Excel.
Nếu bạn nhập một mã zip như ”00123” trong một ô, Excel sẽ tự động cắt thành ”123”. Chắc chắn bạn sẽ cần phải giữ lại các số 0 ở đầu các ô 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.
Vì Microsoft Excel xử lý mã bưu điện, số điện thoại và các mục tương tự như cách xử lý số, vấn đề này xảy ra và tự động áp dụng định dạng tổng quát như là tự xóa các số 0 đứng ở đầu. Rất may, Excel cũng cung cấp cho chúng ta cách để giữ lại các số 0 đứng ở đầu các ô. Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách đó.
Table of Contents
Cách giữ lại các số không đứng đầu khi nhập vào Excel
Thay đổi định dạng ô thành Text: là phương pháp đơn giản để giữ lại các số 0 đứng đầu trong một ô, ví dụ như 01. Những người mới bắt đầu làm quen với Excel có thể áp dụng phương pháp này.
Cách 1:.
Khi bạn nhập một số bắt đầu bằng số 0, Excel sẽ hiển thị một biểu tượng tam giác nhỏ màu xanh lá ở góc trên cùng bên trái của ô để cho thấy điều này không hợp lệ. Để loại bỏ biểu tượng đó, bạn chọn một hoặc nhiều ô cần quan tâm rồi nhấp vào biểu tượng cảnh báo, sau đó chọn Lờ đi lỗi.
Mọi người hãy xem thêm ảnh phía dưới để nhìn thấy kết quả.
Cách 2:.
Đặt trước nó một biểu tượng (‘) là một phương pháp khác để giữ những số 0 ở đầu trong Excel. Ví dụ, bạn nhập là ’01 thay vì nhập 01, điều này sẽ giúp bạn không cần thay đổi định dạng của ô đó.
Phương pháp này có một số hạn chế đó là kết quả của chuỗi văn bản sẽ không phải là một con số, do đó không thể sử dụng để thực hiện các phép tính và không thể áp dụng các công thức số. Nếu bạn không muốn gặp tình trạng đó, bạn có thể thay đổi các biểu thức giá trị trực quan bằng cách áp dụng định dạng số tùy chỉnh như ví dụ dưới đây. Lưu ý:
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
Các bạn có thể sử dụng định dạng số tuỳ chỉnh bằng cách làm theo các bước sau để hiển thị số 0 đứng đầu.
Dùng mã định dạng 00000, bạn có thể thêm số không vào đầu để tạo một số có năm chữ số. Ví dụ:
Sử dụng mã định dạng, bạn có thể thêm các chữ số không vào đầu để tạo các số có độ dài cố định hoặc không cố định. Điều này có thể được thực hiện bằng cách sử dụng các định dạng số tùy chỉnh của Excel, như đã được minh họa trong ví dụ trên.
0 – hiển thị thêm số không.
Dấu # không hiển thị số 0 được thêm vào.
Dù bạn nhập vào số nào, số đó sẽ bao gồm thêm 3 số không đứng ở đầu nếu bạn sử dụng định dạng #000 với một số ô.
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,….
Trong Excel, ta có thể tạo các cấu hình tùy chỉnh để hiển thị các số bắt đầu với chữ số 0. Dưới đây là một ví dụ về bảng tính sử dụng các cấu hình này.
Các định dạng sau có thể được sử dụng để hiển thị số trong các định dạng đặc biệt như mã zip, số điện thoại, số thẻ tín dụng và số an sinh xã hội.
Dưới đây là biểu thị số an sinh xã hội, số điện thoại và mã bưu điện, gợi ý: Excel có một số định dạng được thiết lập sẵn.
Các số và ngày tháng có thể sử dụng các định dạng tuỳ chỉnh và kết quả sẽ là số hoặc ngày tháng. Phương pháp này tốt nhất khi làm việc với dữ liệu số và kết quả là số chứ không phải văn bản. Phương pháp này chỉ thay đổi cách hiển thị một số, nhưng không thay đổi giá trị của số đó: mặc 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 vẫn được giữ nguyên. Khi tính toán các ô chứa các số đó, phép tính sẽ được thực hiện với giá trị ban đầu.
Cách thêm số 0 vào đầu trong Excel sử dụng hàm TEXT
Số đó của chất bản cả đổi làm thay dạng theo đó số vào 0 số thêm thì TEXT Excel hàm còn đó số của cơ trị giá đổi làm thay đổi không mà số của cơ trước phía 0 số hiển thị phép cho tùy chỉnh số dạng.
Bạn dùng hàm TEXT(value, format_text) để thêm các số không vào đầu một số. Các định dạng được sử dụng tương tự như các định dạng số tùy chỉnh. Tuy nhiên, kết quả của hàm TEXT luôn là một chuỗi văn bản, ngay cả khi nó có dạng giống một số.
Ví dụ, để thêm số 0 vào trước một giá trị trong ô A2, ta sử dụng công thức:.
= VĂN BẢN (A2, ”0 #”).
Input: Sử dụng chuỗi sau để tạo chuỗi số 0 đứng trước với độ dài cố định là 5 ký tự.
= VĂN BẢN (A2, ”000000”).
Sau đây và kết quả thu được nằm trong dấu ngoặc kép định dạng mã cần TEXT chú ý cũng cần bạn.
Lưu ý: Hàm TEXT trong Excel luôn trả về một chuỗi văn bản thay vì một số, vì vậy bạn không thể sử dụng nó trong các phép tính số học và các công thức khác, mà chỉ có thể sử 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
Sao vậy chỉnh định dạng không được, không có cách nào sao? 0A102 ví dụ.
Có thể sử dụng một trong những phương pháp sau đây, nếu giá trị cần thêm 0 vào chứa các ký tự chữ hoặc văn bản. Các phương pháp này là giải pháp tổng quát cho cả số và chuỗi văn bản.
Công thức 1: Thêm các số 0 vào đầu sử dụng hàm RIGHT
Hàm RIGHT: là phương pháp đơn giản nhất để đưa số 0 lên đầu các chuỗi văn bản trong Excel.
RIGHT(”0000” & cell, string_length).
Trong đó:.
Bạn nhập ”00” để thêm vào 2 số 0. ”0000” là số số không tối đa mà bạn muốn thêm vào.
Cell là một ô trỏ tới ô chứa giá trị ban đầu.
String_length là độ dài của chuỗi kết quả.
Công thức được sử dụng để 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 là ví dụ.
= RIGHT (”000000” & A2, 6).
Trong chuỗi, trước khi bổ sung 6 số không vào giá trị chứa trong ô A2 (“000000″&A2), công thức này sẽ lấy ra chính xác 6 ký tự. Kết quả là nó chỉ thêm đúng số số không cần thiết để đạt đến giới hạn tổng số ký tự trong chuỗi. Cách hoạt động của công thức này là bổ sung 6 số không vào giá trị chứa trong ô A2, sau đó lấy ra chính xác 6 ký tự.
Công thức trả về một chuỗi gồm 6 số không nếu thực hiện cho một ô trống. Vì vậy, số số không tối đa trong ví dụ trên bằng tổng độ dài của chuỗi ký tự là 6 ký tự. Do đó, tất cả các chuỗi kết quả đều có độ dài là 6 ký tự và độ dài này là không thay đổi.
Ví dụ, bạn có thể thêm số không giống nhau và thay đổi tổng số ký tự theo ý muốn, tùy thuộc vào mục đích sử dụng.
=RIGHT(”00″&A2, 6).
Một chuỗi kết quả thu được có độ dài thay đổi, có thể chứa tối đa hai con số không ở vị trí đầ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
Sử dụng sự phối hợp giữa hàm ĐỘ_DÀI và hàm LẶP là một phương pháp khác để thêm số 0 vào đầu một chuỗi văn bản trong Excel.
REPT(0, số lượng số 0-ĐỘ DÀI(cell))&cell.
Công thức bạn dùng để thêm các số 0 vào một giá trị trong ô A2 để tạo chuỗi có 6 ký tự là ví dụ:
=REPT(0, 6-LEN(A2))&A2.
Cách hoạt động của công thức này như sau:
Theo nguyên tắc dễ hiểu, ta có công thức: Hàm LEN trả về tổng độ dài của chuỗi ký tự và hàm REPT lặp lại một ký tự cho trước một số lần nhất định.
Hàm LEN(A2) trả về tổng số ký tự trong ô A2.
Kết quả cuối cùng mà chúng ta thu được là: Để tính toán số lượng số 0 cần thêm vào, bạn trừ số lượng số không tối đa đi độ dài của chuỗi trong ô A2. Sau đó, sử dụng hàm REPT(0, 6-LEN(A2)) để thêm vào số lượng số không không cần thiết.
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ố. Chú ý.
Cách thêm một số lượng số không cố định vào đầu giá trị
Sử dụng hàm GHÉP HOẶC hoặc hàm toán tử, bạn có thể 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.
Ví dụ, để gán giá trị 0 cho một số trong ô A2, sử dụng các biểu thức sau:.
=CONCATENATE(0,A2).
Hoặc.
=0&A2.
Trong hình ảnh chụp màn hình bên dưới, phương pháp thêm chỉ một con số không vào đầu hàng cho tất cả các ô trong một cột dù ban đầu những ô đó chứa bất kỳ số ký tự nào, giống như những gì được hiển thị.
Các con số hoặc chuỗi văn bản có thể thêm vào 2 con số không (00), 3 con số không (000) hoặc nhiều con số không trước đó. Tương tự, bạn có thể chèn.
Cũng chú ý rằng, khi bổ sung giá trị ban đầu là số, kết quả của công thức này vẫn là một chuỗi từ văn bản.
Cách loại bỏ số không đứng đầu trong Excel
Cách mà các số không nằm ở vị trí đầu trong Excel được bổ sung vào phụ thuộc vào phương pháp bạn sử dụng để loại bỏ chúng.
Bạn sử dụng một định dạng tuỳ chỉnh khác hoặc đặt lại định dạng General nếu trước đó, các số không được thêm vào bằng cách sử dụng định dạng số tuỳ 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).
Bạn chuyển đổi văn bản đó thành dạng số nếu các số không được nhập vào ô được định dạng ở mục Text (xuất hiện hình tam giác nhỏ màu xanh lá cây ở góc trên cùng bên trái của ô).
Loại trừ chúng bằng cách sử dụng hàm VALUE, 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 ô đó).
Các bạn xem hình phía dưới để hiểu rõ hơn về 3 trường hợp trên và chọn phương pháp thích hợp.
Loại bỏ các số không đứng đầu bằng cách thay đổi định dạng ô
Các ô có định dạng tuỳ chỉnh, nếu không đứng đầu, hãy thay đổi định dạng thành mặc định chung hoặc áp dụng định dạng khác để số không được hiển thị ở đầu giá trị ô.
Loại bỏ các số không đứng đầu bằng cách chuyển sang dạng số
Chọn điểm có dấu chấm đen, nhấn vào một hoặc nhiều ô, sau đó chọn Chuyển đổi thành Số để loại bỏ những con số không xuất hiện ở đầu giá trị trong một ô đã được định dạng thành văn bản.
Loại bỏ các số không đứng đầu bằng cách sử dụng công thức
Dễ dàng khá các loại để giữ bằng cách sử dụng một công thức cần thiết, bạn có thể thêm vào đó nhiều số hoặc một trường hợp phù hợp.
=VALUE(A2).
Trong đó A2 là ô mà bạn muốn loại bỏ các số không đứng đầu.
Có thể áp dụng cách này để loại bỏ những số không được gõ trực tiếp vào ô hoặc nhập vào Excel từ một số nguồn bên ngoài. Nói chung, nếu bạn muốn thực hiện các thao tác với dãy số không đứng ở đầu, có thể sử dụng hàm VALUE để chuyển đổi văn bản thành số và loại bỏ những số đó.
Bạn có thể xem hai cách thức trên hình ảnh bên dưới.
Công thức Văn bản ở ô B2 để thêm số 0 vào đầu giá trị.
Công thức VALUE ở ô C2 để xóa các số không được thêm vào.
Cách ẩn các số không trong Excel
Nếu bạn không muốn hiển thị các giá trị không có trong bảng tính Excel thì có hai phương án như sau:.
B2 trong hình ảnh chụp màn hình phía dưới có giá trị là 0 nhưng không được hiển thị trong ô.
Xin chân thành cảm ơn các bạn đã quan tâm đến bài viết này. Dưới đây là nội dung về cách thêm, xoá và ẩn các số không trong Excel.
Chúng ta không chỉ cần hiểu rõ các hàm mà còn cần sử dụng hiệu quả các công cụ của Excel để áp dụng vào công việc. Các công cụ thường được sử dụng bao gồm xác thực dữ liệu, định dạng có điều kiện, bảng tổng hợp… Các hàm nâng cao như TỔNGIF, ĐẾMIF, TÍCHSẢNPHẨM, INDEX + SO KHỚP cũng hỗ trợ tốt cho công việc.
Học Excel trực tuyến tại chuyên gia đến cơ bản từ học khóa trong được học bạn các này kiến thức tất cả đều có thể.
