Đôi khi nhập số 0 ở đầu ô dữ liệu trong Excel nhưng khi nhấn Enter thì thấy chỉ hiện ra những số sau số 0. Điều này khiến bạn cảm thấy vô cùng khó chịu vì bảng tính trông rất thiếu khoa học. Sau đây, Unica sẽ Hướng dẫn cách thêm số 0 vào đầu giá trị trong excel siêu dễ dàng. Cùng tìm hiểu ngay.
Cách thêm số 0 vào đầu dãy số trong excel
Thêm số 0 bằng sử dụng hàm TEXT
Hàm Text được sử dụng để chuyển đổi một giá trị số thành định dạng Văn bản bằng cách sử dụng các chuỗi định dạng đặc biệt. Đây là cách áp dụng rất phổ biến được dân văn phòng ứng dụng nhiều mỗi khi gặp vấn đề mất số 0 ở đầu số điện thoại, định dạng số điện thoại trong Excel giúp bạn có thể sử dụng dễ dàng hơn.
Công thức hàm TEXT:
=TEXT(value, format_text)
Trong đó:
- value: là giá trị mà bạn muốn định dạng.
- format_text: là chuỗi ký tự mô tả cách bạn muốn định dạng giá trị.
Ví dụ 1: Nếu bạn muốn thêm số 0 trước số điện thoại 0987654321 và hiển thị 10 chữ số, bạn có thể sử dụng công thức sau:
=TEXT(987654321, “0000000000”)
Kết quả sẽ là 0987654321.
Ví dụ 2: Cho bảng số liệu như sau"
Cho bảng số liệu
Yêu cầu thêm số 0 vào trước các số điện thoại bằng hàm Text.
Bước 1: Ở ô B2 nhập công thức là =TEXT(A2;"0000000000")
Nhập công thức Text
Bước 2: Nhấn enter sẽ thu được kết quả như hình dưới đây
Nhấn enter
Bước 3: Kéo chuột xuống bên dưới để copy công thức, bảng kết quả cuối cùng bạn thu được sẽ như sau:
Kết quả nhận được
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
Thêm số 0 bằng công cụ Format Cell
Trên thanh công cụ có tính năng Format Cell có tác dụng định dạng ô trong Excel. Cách này thực hiện khá nhanh chóng để giúp hiển thị số 0 trong số điện thoại.
Cách thêm số 0 trong excel bằng tính năng này như sau:
Bước 1: Nhấn chọn mục Format trên thanh công cụ Home
Ở thanh xổ xuống, chọn lệnh Format Cell.
Chọn lệnh Format Cell
Trong bảng chọn Number, bạn chọn Custom, trong phần Type gõ 10 số 0, tương ứng với tổng số điện thoại để thêm số 0 vào số điện thoại.
Sau đó bạn ấn OK.
Cách thêm số 0 trong excel bằng công cụ Format Cell
Bước 2: Bạn có thể thấy rằng ô đã được định dạng trực tiếp với số 0 ở trước mỗi số. Những thay đổi này đã được thực hiện trong chính cột A mà không cần sang cột khác. Thêm số 0 vào số điện thoại trong excel bằng cách này rất đơn giản đúng không nào, đồng thời cũng giúp cho cách nhập số điện thoại trong excel đơn giản và nhanh chóng hơn.
Kết quả thêm số 0 đằng trước sđt
Thêm số 0 bằng dấu ' trước dãy số
Một cách đơn giản nhất để thêm số 0 trước số điện thoại trong Excel là thêm dấu nháy đơn (‘) đằng trước dãy số.
Ví dụ: Nếu bạn muốn định dạng số điện thoại trong excel là 982167587, bạn chỉ cần gõ ‘982167587 vào ô Excel. Lúc này, Excel sẽ hiểu rằng bạn muốn nhập một chuỗi văn bản, không phải một số, và sẽ giữ nguyên số 0 đầu tiên.
Hình ảnh minh họa
Tuy nhiên, cách này có một hạn chế là bạn không thể sử dụng các hàm tính toán hay lọc dữ liệu với các ô chứa dấu nháy đơn. Nếu bạn muốn làm được điều này, bạn có thể sử dụng các cách khác sau đây.
Thêm số 0 bằng hàm CONCATENTE
Một cách khác để thêm số 0 trước số điện thoại trong Excel là sử dụng hàm CONCATENATE. Hàm CONCATENATE có công thức như sau:
=CONCATENATE(text1, text2, …)
Trong đó:
- text1, text2, … là các chuỗi văn bản mà bạn muốn nối lại với nhau.
Ví dụ: Giả sử, bạn muốn thêm số 0 vào trước sdt trong bảng bên dưới bằng cách dùng hàm CONCATENATE.
Bước 1: Trong cột mô tả số điện thoại đầy đủ, tại ô đầu tiên trong cột số điện thoại, bạn nhập hàm =CONCATENATE("0";B6).
Nhập công thức hàm CONCATENATE
Nhấn enter là số điện thoại đã tự động thêm số 0 đằng trước.
Số điện thoại đã được thêm số 0 đằng trước
Bước 2: Click vào biểu tượng dấu + và rê chuột để sao chép công thức xuống các dòng bên dưới.
Nhấn vào biểu tượng dấu cộng bên dưới và kéo xuống các ô phía dưới
Toàn bộ các ô bên dưới sẽ được copy công thức nên sẽ chuyển thành dãy số điện thoại được thêm số 0 như hình dưới đây:
Toàn bộ số điện thoại sẽ được thêm số 0 đằng trước
Bước 3: Sao chép cột số điện thoại đầy đủ bằng cách nhấn chuột phải và chọn "Copy"
Chọn biểu tượng copy
Bước 4: Dán số điện thoại đã sao chép vào cột số điện thoại cũ. Chọn định dạng dán như hình mô tả để không dán cả công thức vào cột số điện thoại cũ.
Chọn định dạng số
Bước 5: Xóa cột số điện thoại chứa công thức hàm.
Xóa cột số điện thoại chứa công thức hàm
Bảng kết quả cuối cùng:
Bảng kết quả cuối cùng
Cách thêm số 0 vào excel này có lợi là bạn có thể thêm bất kỳ ký tự nào vào đầu các ô chứa số điện thoại, không chỉ là số 0. Bạn cũng có thể nối nhiều chuỗi văn bản lại với nhau để tạo ra các định dạng khác nhau.
Lưu ý: Kết quả trả về của hàm CONCATENATE là một chuỗi văn bản, không phải một số. Do đó, bạn không thể sử dụng các hàm tính toán hay lọc dữ liệu với các ô chứa hàm CONCATENATE.
Thêm số 0 bằng thiết lập định dạng "0"#
Bước 1: Chọn ô mà bạn muốn nhập số điện thoại. Sau đó nhấn chuột phải và chọn "Format Cells" (Ctrl +1).
Chọn Format Cells
Bước 2: Ở mục Category, chọn Custom và nhập "0#' vào ô Type sau đó nhấn OK là hoàn thành thêm số 0 vào đầu trong excel.
Nhập "0#' vào ô Type sau đó nhấn OK
Lưu ý: Ký tự # xuất hiện trong định dạng này sẽ biểu thị một chữ số tuỳ ý, còn dấu "0" đặt ở đằng trước có chức năng đảm bảo rằng mỗi chữ số sẽ hiển thị, bao gồm cả số 0 ở đằng trước.
Khi bạn nhập một dãy số ở đây, Excel sẽ hiển thị số 0 trước nó, đồng thời cũng sẽ duy trì chiều dài của số. Như vậy, nó rất thích hợp để định dạng với số điện thoại có độ dài cố định.
Bảng kết quả cuối cùng sau khi thêm số 0
Cách thêm số 0 vào đầu chuỗi văn bản trong excel
Ngoài việc thêm số 0 vào đầu các ô chứa số điện thoại hay mã sản phẩm, bạn cũng có thể muốn thêm số 0 vào đầu các ô chứa chuỗi văn bản. Ví dụ, bạn có một danh sách mã sinh viên gồm 8 ký tự, nhưng có một số mã chỉ gồm 7 ký tự. Bạn muốn thêm số 0 vào đầu các mã này để tạo ra một danh sách đồng nhất.
Để thêm số 0 vào đầu excel, bạn có thể sử dụng hai công thức sau:
Thêm số 0 bằng hàm RIGHT
Công thức hàm Right:
=RIGHT(text, num_chars)
Trong đó:
- text: là chuỗi văn bản mà bạn muốn lấy một số ký tự ở cuối.
- num_chars: là số ký tự mà bạn muốn lấy ở cuối chuỗi văn bản.
Ví dụ: Cho bảng giá trị sau. Yêu cầu là thêm số 0 đằng trước các dãy số ở ô Giá trị ban đầu.
Với dãy số 123456, ta sẽ áp dụng công thức ở cột B4 là:
B4 = RIGHT("0"&A4;7) = 0123456
Áp dụng công thức thêm số 0 trước số trong excel tương tự ở ô B4 để tính các dãy số bên dưới. Sẽ có bảng kết quả sau:
Kết quả giá trị được chèn thêm số 0
Thêm số 0 hàm REPT và LEN
Ngoài những cách thêm số 0 đã chia sẻ ở trên, bạn cũng có thể sử dụng hàm REPT và LEN để thêm số 0 vào đầu văn bản.
-
Hàm LEN: hàm đếm số ký tự trong một dãy ký tự hoặc trong một văn bản.
Cú pháp hàm LEN:
=LEN(dãy ký tự hoặc văn bản).
-
Hàm REPT: hàm lặp lại chuỗi ký tự, từ, số cho trước sao cho dãy kết quả cuối cùng đủ số ký tự được chỉ định.
Cú pháp hàm REPT:
=REPT(văn bản muốn lặp, số lần lặp).
Công thức hàm REPT và LEN:
=REPT(“0”, num_chars - LEN(text))&text
Trong đó:
- text: là chuỗi văn bản mà bạn muốn thêm số 0 vào đầu.
- num_chars: là số chữ số tối đa mà bạn muốn hiển thị.
Ví dụ: Tại ô A1 đang chứa dãy ký tự là AB01 bao gồm 4 ký tự có lẫn chữ và số. Nhiệm vụ của bạn là thêm 2 số 0 vào trước để thành dãy số 00AB01.
Khi này, để thêm được số 0 bạn sẽ nhập công thức: =REPT(0,6-LEN(A1))&A1
Trong đó:
-
0: ký tự bạn muốn thêm vào.
-
6-LEN(A1): chuỗi văn bản bạn muốn thêm số 0. Vì ô A1 đang có sẵn 4 ký tự, bạn muốn thêm 2 ký tự 0 thì nên nhập 6.
-
&A1: ghép kết quả tính được từ hàm REPT với dãy kí tự của ô A1.
Nhập công thức để thêm số 0 vào đầu văn bản
Cách loại bỏ, ẩn số 0 đứng đầu trong Excel
Ngoài việc chèn số 0 vào số điện thoại trong excel hay mã sản phẩm, bạn cũng có thể muốn loại bỏ các số 0 không cần thiết đứng đầu các ô chứa số hay chuỗi văn bản. Ví dụ, bạn có một danh sách mã sản phẩm gồm 8 ký tự, nhưng có một số mã có các số 0 đứng đầu, như “00012345” hay “00123456”. Bạn muốn loại bỏ các số 0 này để tạo ra một danh sách gọn gàng hơn.
Để làm được điều này, bạn có thể sử dụng ba cách sau:
Bỏ số 0 bằng cách thay đổi định dạng ô
Một cách đơn giản nhất để loại bỏ các số không đứng đầu trong Excel là thay đổi định dạng ô từ Custom (hoặc Tùy chỉnh) sang General (hoặc Chung). Bạn có thể làm theo các bước sau:
Bước 1: Chọn các ô chứa số hay chuỗi giá trị mà bạn muốn loại bỏ các số 0 đứng đầu.
Bôi đen vùng dữ liệu cần xóa số 0
Bước 2: Nhấn chuột phải và chọn Format Cells (hoặc nhấn Ctrl + 1).
Mở hộp thoại Format Cells
Bước 3: Trong hộp thoại Format Cells, chọn tab Number và chọn General (hoặc Chung).
Bước 4: Nhấn OK để xác nhận.
Nhấn OK
Lúc này, Excel sẽ tự động loại bỏ các số 0 đứng đầu các ô chứa số hay chuỗi văn bản. Cách này có lợi là bạn không cần sử dụng bất kỳ công thức nào, chỉ cần thay đổi một lần định dạng ô.
Tuy nhiên, cách này có một hạn chế là nếu bạn muốn lưu lại định dạng ô ban đầu, bạn sẽ phải thay đổi lại sau khi loại bỏ các số 0. Ngoài ra, cách này không áp dụng được cho các ô chứa hàm hay công thức, mà chỉ áp dụng được cho các ô chứa giá trị tĩnh.
Bỏ số 0 bằng cách chuyển sang dạng số
Một cách khác để loại bỏ các số không đứng đầu trong Excel là chuyển các ô chứa số hay chuỗi văn bản sang dạng số. Cách thêm số 0 trong excel này gồm các bước sau:
Bước 1: Chọn các ô chứa số hoặc bảng giá trị mà bạn muốn loại bỏ các số 0 đứng đầu.
Bôi đen vùng dữ liệu cần xóa số 0
Bước 2: Nhấn chuột phải và chọn Copy (hoặc nhấn Ctrl + C).
Copy dữ liệu
Bước 3: Chọn một ô trống nào đó và nhấn chuột phải và chọn Paste Special (hoặc nhấn Ctrl + Alt + V).
Mở hộp thoại Paste Special
Bước 4: Trong hộp thoại Paste Special, chọn Values (hoặc Giá trị) và nhấn OK.
Nhấn chọn OK
Lúc này, Excel sẽ sao chép giá trị của các ô đã chọn sang một vùng mới và tự động loại bỏ các số 0 đứng đầu.
Kết quả
Cách này có lợi là bạn có thể giữ nguyên định dạng ô ban đầu của các ô đã chọn, và chỉ tạo ra một bản sao mới đã loại bỏ các số 0.
Tuy nhiên, cách này cũng có một hạn chế là bạn sẽ phải tạo ra một vùng mới để sao chép giá trị, và không thể sử dụng lại vùng ban đầu. Ngoài ra, cách này không áp dụng được cho các ô chứa hàm hay công thức, mà chỉ áp dụng được cho các ô chứa giá trị tĩnh.
Bỏ số 0 bằng cách sử dụng công thức
Một cách nữa để loại bỏ các số không đứng đầu trong Excel là sử dụng công thức. Bạn có thể sử dụng hai hàm sau:
Hàm VALUE có công thức như sau:
=VALUE(text)
Trong đó:
- text: là chuỗi văn bản mà bạn muốn chuyển sang dạng số.
Ví dụ: Loại bỏ số 0 trong bảng giá trị sau bằng cách dùng hàm Value:
Cho bảng giá trị
Bước 1: Nhập hàm Value vào ô C4 như hình:
Nhập hàm value
Bước 2: Nhấn enter sẽ thu được kết quả như sau:
Nhấn enter
Bước 3: Copy công thức xuống các ô bên dưới sẽ có được bảng kết quả như sau:
Copy công thức xuống các ô
Tuy nhiên, cách này cũng có một hạn chế là kết quả trả về của hàm VALUE hoặc NUMBERVALUE là một số, không phải một chuỗi văn bản. Do đó, bạn sẽ mất đi các ký tự khác trong chuỗi văn bản, như dấu gạch ngang (-), dấu chấm (.), dấu phẩy (,), v.v.
Ẩn số 0 đứng đầu trong Excel
Ngoài việc loại bỏ các số 0 đứng đầu trong Excel, bạn cũng có thể muốn ẩn các số 0 không cần thiết trong các ô chứa giá trị bằng 0. Ví dụ, bạn có một bảng tính toán doanh thu của một cửa hàng, nhưng có một số ngày không có doanh thu nào. Bạn muốn ẩn các giá trị 0 này để tạo ra một bảng tính gọn gàng hơn.
Để làm được điều này, bạn có thể ẩn các số 0 trong Excel bằng cách thay đổi tùy chọn hiển thị. Các bước làm như sau:
Bước 1: Nhấn nút File (hoặc Office) trên thanh công cụ của Excel.
Nhấn nút File
Bước 2: Chọn Options (hoặc Excel Options) để mở hộp thoại Excel Options.
Mở hộp thoại Excel Options
Bước 3: Trong hộp thoại Excel Options, chọn Advanced (hoặc Nâng cao) và kéo xuống phần Display options for this worksheet (hoặc Tùy chọn hiển thị cho bảng tính này).
Kéo xuống phần Display options for this worksheet
Bước 4: Bỏ chọn ô Show a zero in cells that have zero value (hoặc Hiển thị số 0 trong các ô có giá trị bằng 0).
Bỏ chọn ô Show a zero in cells that have zero value
Bước 5: Nhấn OK để xác nhận.
Nhấn OK
Lúc này, Excel sẽ tự động ẩn các số 0 trong các ô chứa giá trị bằng 0. Cách này có lợi là bạn không cần sử dụng bất kỳ công thức nào, chỉ cần thay đổi một lần tùy chọn hiển thị của Excel.
Tuy nhiên, cách này có một hạn chế là nó chỉ áp dụng cho một bảng tính cụ thể, không áp dụng cho toàn bộ workbook. Nếu bạn muốn áp dụng cho toàn bộ workbook, bạn sẽ phải lặp lại các bước trên cho mỗi bảng tính. Ngoài ra, cách này không thay đổi giá trị của các ô chứa số 0, mà chỉ thay đổi cách hiển thị của chúng.
Kết luận
Như vậy, Unica đã hướng dẫn cách thêm số 0 trong Excel đơn giản và hiệu quả, cũng như cách loại bỏ hoặc ẩn các số 0 không mong muốn. Bạn có thể sử dụng các cách này tùy theo mục đích và nhu cầu của bạn. Hy vọng bài viết này sẽ giúp bạn sử dụng Excel một cách hiệu quả hơn.
Bên cạnh đó Unica còn mang đến bạn đọc những kiến thức về các khoá học Excel từ cơ bản đến nâng cao đang chờ bạn khám phá.
>>> Xem thêm:
_m_1687340871.jpg)
