Hướng dẫn này bao gồm các phương pháp khác nhau để thêm hoặc giữ các số không đứng đầu cũng như xóa các số không đứng đầu trong Excel.
Bất cứ khi nào bạn nhập hoặc nhập các số có một hoặc nhiều số không ở đầu, chẳng hạn như 000652, Excel sẽ tự động loại bỏ các số không đó và chỉ số tự động hiển thị trong các ô (652). Điều này là do các số 0 ở đầu không cần thiết cho các phép tính và không được tính.
Tuy nhiên, đôi khi những số không đứng đầu đó là cần thiết, như khi bạn nhập số ID, số điện thoại, số thẻ tín dụng, mã sản phẩm hoặc mã bưu điện, v.v. May mắn thay, Excel cung cấp cho chúng ta một số cách để thêm hoặc giữ các số không ở đầu trong các ô. Trong bài viết này, Chúng tôi sẽ chỉ cho bạn các cách khác nhau để thêm hoặc giữ các số không ở đầu và loại bỏ các số không ở đầu.
Thêm Zeros hàng đầu trong Excel
Về cơ bản, có 2 phương pháp mà bạn có thể sử dụng để thêm các số 0 ở đầu: một, định dạng số của bạn là 'Văn bản'; hai, sử dụng định dạng tùy chỉnh để thêm các số 0 ở đầu. Phương pháp bạn muốn sử dụng có thể phụ thuộc vào những gì bạn muốn làm với số.
Bạn có thể muốn thêm số 0 ở đầu khi nhập số ID duy nhất, số tài khoản, số an sinh xã hội hoặc mã zip, v.v. Tuy nhiên, bạn sẽ không sử dụng những số này để tính toán hoặc trong các hàm, vì vậy tốt nhất là chuyển đổi những số để văn bản. Bạn sẽ không bao giờ tính tổng hoặc trung bình số điện thoại hoặc số tài khoản.
Có một số cách bạn có thể thêm hoặc thêm các số không vào trước các số bằng cách định dạng chúng dưới dạng văn bản:
- Thay đổi định dạng ô thành Văn bản
- Thêm dấu nháy đơn (‘)
- Sử dụng chức năng TEXT
- Sử dụng hàm REPT / LEN
- Sử dụng hàm CONCATENATE / Ký hiệu và toán tử (&)
- Sử dụng chức năng RIGHT
Thay đổi định dạng ô thành văn bản
Đây là một trong những cách đơn giản nhất để thêm các số không ở đầu các số của bạn. Nếu bạn chỉ định nhập số và bạn muốn giữ các số 0 ở đầu khi bạn nhập, thì đây là phương pháp dành cho bạn. Bằng cách thay đổi định dạng ô từ Chung hoặc Số thành Văn bản, bạn có thể buộc Excel coi các số của bạn là giá trị văn bản và bất kỳ thứ gì bạn nhập vào ô sẽ giữ nguyên như cũ. Đây là cách bạn thực hiện:
Chọn (các) ô mà bạn muốn thêm các số 0 ở đầu. Đi tới tab ‘Trang chủ’, nhấp vào hộp thả xuống ‘Định dạng’ trong nhóm Số và chọn ‘Văn bản’ từ các tùy chọn định dạng.
Bây giờ khi bạn nhập các số của mình, Excel sẽ không xóa bất kỳ số 0 đứng đầu nào khỏi nó.
Bạn có thể thấy một hình tam giác nhỏ màu xanh lục (Chỉ báo lỗi) ở góc trên cùng bên trái của ô và khi bạn chọn ô đó, nó sẽ hiển thị cho bạn một dấu hiệu cảnh báo cho biết rằng bạn đã lưu trữ số dưới dạng văn bản.
Để loại bỏ thông báo lỗi, hãy chọn (các) ô, bấm vào dấu hiệu cảnh báo, sau đó chọn ‘Bỏ qua lỗi’ từ danh sách.
Bạn cũng có thể nhập các số điện thoại với khoảng trắng hoặc gạch nối giữa các số, Excel sẽ tự động coi những số này là văn bản.
Sử dụng hàng đầu Dấu nháy đơn (‘)
Một cách khác để thêm các số không ở đầu trong Excel là thêm dấu nháy đơn (‘) vào đầu số. Điều này sẽ buộc Excel nhập một số dưới dạng văn bản.
Chỉ cần nhập dấu nháy đơn trước bất kỳ số nào và nhấn "Enter". Excel sẽ giữ nguyên các số không ở đầu, nhưng dấu (‘) sẽ không hiển thị trong trang tính trừ khi bạn chọn ô.
Sử dụng chức năng văn bản
Phương pháp trên thêm các số không vào các số khi bạn nhập chúng, nhưng nếu bạn đã có danh sách các số và bạn muốn thêm các số 0 ở đầu trước chúng, thì hàm TEXT là phương pháp phù hợp với bạn. Chức năng TEXT cho phép bạn chuyển đổi số thành chuỗi văn bản trong khi áp dụng định dạng tùy chỉnh.
Cú pháp của hàm TEXT:
= TEXT (giá trị, định dạng_text)Ở đâu,
- giá trị - Đây là giá trị số mà bạn cần chuyển đổi thành văn bản và áp dụng định dạng.
- format_text - là định dạng bạn muốn áp dụng.
Với chức năng TEXT, bạn có thể chỉ định số của bạn phải có bao nhiêu chữ số. Ví dụ: nếu bạn muốn các số của mình dài 8 chữ số, thì hãy nhập 8 số không vào đối số thứ hai của hàm: “00000000”. Nếu bạn có 6 chữ số trong một ô, thì hàm sẽ tự thêm 2 số 0 ở đầu và nếu bạn có 2 chữ số như 56, phần còn lại sẽ là số 0 (00000056).
Ví dụ: để thêm các số 0 ở đầu và tạo các số có 6 chữ số, hãy sử dụng công thức sau:
= TEXT (A2, "000000")Vì chúng ta có 6 số không trong đối số thứ hai của công thức, hàm chuyển đổi chuỗi số thành chuỗi văn bản và thêm 5 số 0 ở đầu để làm cho chuỗi dài 6 chữ số.
Ghi chú: Hãy nhớ đặt mã định dạng trong dấu ngoặc kép trong hàm.
Bây giờ bạn có thể áp dụng cùng một công thức cho các ô còn lại bằng cách kéo chốt điền. Như bạn có thể thấy, hàm chuyển đổi các số thành văn bản và thêm các số không ở đầu các số để tổng số chữ số là 6.
Hàm TEXT sẽ luôn trả về giá trị dưới dạng chuỗi văn bản, không phải số, vì vậy bạn sẽ không thể sử dụng chúng trong các phép tính số học nhưng bạn vẫn có thể sử dụng chúng trong các công thức tra cứu như VLOOKUP hoặc INDEX / MATCH để tìm nạp chi tiết của một sản phẩm sử dụng ID sản phẩm.
Sử dụng hàm CONCATENATE / Ký hiệu và toán tử (&)
Nếu bạn muốn thêm một số không cố định ở đầu trước tất cả các số trong một cột, bạn có thể sử dụng hàm CONCATENATE hoặc toán tử dấu và (&).
Cú pháp của hàm CONCATENATE:
= CONCATENATE (text1, [text2], ...)Ở đâu,
Văn bản 1 - Số lượng các số không sẽ được chèn vào trước số.
text2 - Số gốc hoặc tham chiếu ô
Cú pháp của toán tử Dấu và:
= Giá trị_1 và Giá trị_2 Ở đâu,
Giá trị_1 là các số 0 ở đầu để chèn trước số và Giá trị 2 là số.
Ví dụ: để chỉ thêm hai số không trước một số, hãy sử dụng một trong công thức sau:
= CONCATENATE ("00", A2)Đối số đầu tiên là hai số không (“00”) vì chúng tôi muốn thêm hai số không vào trước số trong A2 (là đối số thứ hai).
Hoặc là,
= "00" & A2Ở đây, đối số đầu tiên là 2 số không, tiếp theo là toán tử ‘&’ và đối số thứ hai là số.
Như bạn có thể thấy, công thức chỉ thêm hai số không ở đầu cho tất cả các số trong một cột bất kể số đó chứa bao nhiêu chữ số.
Cả hai công thức này đều nối một số số không nhất định trước các số ban đầu và lưu trữ chúng dưới dạng chuỗi văn bản.
Sử dụng hàm REPT / LEN
Nếu bạn muốn thêm các số 0 ở đầu vào dữ liệu số hoặc chữ và số và chuyển đổi chuỗi thành văn bản, thì hãy sử dụng hàm REPT. Hàm REPT được sử dụng để lặp lại (các) ký tự một số lần cụ thể. Hàm này cũng có thể được sử dụng để chèn các số không cố định ở đầu trước số.
= REPT (văn bản, số_lần)Trong đó ‘text’ là ký tự chúng ta muốn lặp lại (trong trường hợp của chúng ta là ‘0’) và đối số ‘number_times’ là số lần chúng ta muốn lặp lại ký tự đó.
Ví dụ: để tạo năm số không trước các số, công thức sẽ giống như sau:
= REPT (0,5) & A2Những gì công thức làm là lặp lại 5 số không và nối chuỗi số trong A2 và trả về kết quả. Sau đó, công thức được áp dụng cho ô B2: B6 bằng cách sử dụng chốt điền.
Công thức trên thêm một số lượng không cố định trước số đó, nhưng tổng độ dài của số khác nhau tùy thuộc vào số.
Nếu bạn muốn thêm các số 0 ở đầu bất cứ nơi nào cần thiết để tạo chuỗi dài (độ dài cố định) của một ký tự cụ thể, bạn có thể sử dụng các hàm REPT và LEN cùng nhau.
Cú pháp:
= REPT (văn bản, số_lần-LEN (văn bản)) & ôVí dụ: để thêm các số 0 có tiền tố vào giá trị trong A2 và tạo một chuỗi dài 5 ký tự, hãy thử công thức sau:
= REPT (0,5-LEN (A2)) & A2Ở đây, ‘LEN (A2)’ nhận tổng độ dài của chuỗi / số trong ô A2. ‘5’ là độ dài tối đa của chuỗi / số mà ô phải có. Và phần ‘REPT (0,5-LEN (A2))’ thêm số lượng số không bằng cách trừ độ dài của chuỗi trong A2 với số lượng số không tối đa (5). Sau đó, một số 0 được nối trước giá trị của A2 để tạo thành một chuỗi có độ dài cố định.
Sử dụng hàm RIGHT
Một cách khác để đệm các số không ở đầu trước một chuỗi trong Excel là sử dụng hàm RIGHT.
Hàm RIGHT có thể thêm một số số không vào đầu một số và trích xuất N ký tự ngoài cùng bên phải từ giá trị.
Cú pháp:
= RIGHT (văn bản, num_chars)- chữ là ô hoặc giá trị bạn muốn trích xuất các ký tự.
- num_chars là số ký tự cần trích xuất từ văn bản. Nếu đối số này không được đưa ra, thì chỉ ký tự đầu tiên sẽ được trích xuất.
Đối với phương pháp này, chúng tôi nối số lượng số không tối đa với tham chiếu ô chứa chuỗi trong đối số ‘text’.
Để tạo một số có 6 chữ số dựa trên chuỗi số trong A với các số 0 ở đầu, hãy thử công thức sau:
= RIGHT ("0000000" & A2,6)Đối số đầu tiên (văn bản) của công thức thêm 7 số không vào giá trị trong A2 (“0000000” & A2), sau đó trả về 7 ký tự ngoài cùng bên phải, dẫn đến một số số không ở đầu.
Thêm các số 0 ở đầu bằng cách sử dụng Định dạng số tùy chỉnh
Nếu bạn sử dụng bất kỳ phương pháp nào ở trên để đặt các số 0 ở đầu trước các số, bạn sẽ luôn nhận được một chuỗi văn bản, không phải một số. Và chúng sẽ không được sử dụng nhiều trong tính toán hoặc công thức số.
Cách tốt nhất để thêm các số 0 ở đầu trong Excel là áp dụng định dạng số tùy chỉnh. Nếu bạn thêm các số 0 ở đầu bằng cách thêm định dạng số tùy chỉnh vào ô, thì nó không thay đổi giá trị của ô mà chỉ thay đổi cách nó được hiển thị. Giá trị sẽ vẫn ở dạng số, không phải văn bản.
Để thay đổi định dạng số của ô, hãy làm theo các bước sau:
Chọn ô hoặc dải ô mà bạn muốn hiển thị các số không ở đầu. Sau đó, nhấp chuột phải vào bất kỳ đâu trong phạm vi đã chọn đó và chọn tùy chọn ‘Định dạng ô’ từ trình đơn ngữ cảnh. Hoặc nhấn tổ hợp phím tắt Ctrl + 1.
Trong cửa sổ Định dạng Ô, chuyển đến tab ‘Số’ và chọn ‘Tùy chỉnh’ trong tùy chọn Danh mục.
Nhập số lượng số không vào hộp ‘Loại:’ để chỉ định tổng số chữ số bạn muốn hiển thị trong một ô. Ví dụ: nếu bạn muốn số có 6 chữ số, thì hãy nhập ‘000000’ làm mã định dạng tùy chỉnh. Sau đó, nhấp vào ‘OK’ để áp dụng.
Điều này sẽ hiển thị các số 0 ở đầu trước các số và nếu số đó nhỏ hơn 6 chữ số, nó sẽ đặt số 0 trước nó.
Các số sẽ chỉ có các số 0 ở đầu trong khi giá trị cơ bản sẽ không thay đổi. Nếu bạn chọn một ô có định dạng tùy chỉnh, nó sẽ hiển thị cho bạn số ban đầu trong thanh công thức
Có rất nhiều trình giữ chỗ kỹ thuật số mà bạn có thể sử dụng ở định dạng số tùy chỉnh của mình. Nhưng chỉ có hai trình giữ chỗ chính mà bạn có thể sử dụng để thêm các số không ở đầu.
- 0 – Nó là phần giữ chỗ chữ số hiển thị thêm các số không. Nó hiển thị các chữ số bắt buộc từ 0-9 cho dù chữ số đó có liên quan đến giá trị hay không. Ví dụ: nếu bạn nhập 2.5 với mã định dạng 000.00, nó sẽ hiển thị 002.50.
- # – Nó là phần giữ chỗ chữ số hiển thị các chữ số tùy chọn và không bao gồm các số 0 thừa. Ví dụ: nếu bạn nhập 123 với mã định dạng 000 #, nó sẽ hiển thị 0123.
Ngoài ra, mọi dấu chấm câu hoặc ký tự khác mà bạn đưa vào mã định dạng sẽ được hiển thị như nguyên trạng. Bạn có thể sử dụng các ký tự như gạch nối (-), dấu phẩy (,), dấu gạch chéo (/), v.v.
Ví dụ: bạn cũng có thể định dạng số dưới dạng số điện thoại bằng cách sử dụng định dạng tùy chỉnh.
Mã định dạng trong hộp thoại Định dạng ô:
Kết quả:
Hãy áp dụng mã định dạng này trong ví dụ sau:
##0000
Như bạn có thể thấy, ‘0’ sẽ thêm các số 0 bổ sung trong khi ‘#’ không thêm các số 0 không quan trọng:
Bạn cũng có thể sử dụng mã định dạng được xác định trước trong phần 'Định dạng đặc biệt' của hộp thoại Định dạng ô cho mã bưu chính, số điện thoại và số an sinh xã hội.
Bảng sau đây hiển thị các số có số 0 ở đầu trong đó các mã định dạng 'Đặc biệt' khác nhau được áp dụng cho các cột khác nhau:
Loại bỏ các Zeros hàng đầu trong Excel
Bây giờ, bạn đã học cách thêm các số không đứng đầu trong Excel, hãy xem cách xóa các số không đứng đầu khỏi số chuỗi. Đôi khi, khi bạn nhập dữ liệu từ một nguồn bên ngoài, các số có thể có số 0 ở tiền tố và được định dạng dưới dạng văn bản. Trong những trường hợp như vậy, bạn cần loại bỏ các số 0 ở đầu và chuyển đổi chúng trở lại thành số, vì vậy bạn có thể sử dụng chúng trong công thức.
Có nhiều cách khác nhau để bạn có thể xóa các số 0 ở đầu trong Excel và chúng ta sẽ thấy từng số một.
Loại bỏ các Zeros hàng đầu bằng cách thay đổi định dạng ô
Nếu các số 0 ở đầu được thêm vào bằng cách định dạng số tùy chỉnh, thì bạn có thể dễ dàng loại bỏ chúng bằng cách thay đổi định dạng của ô. Bạn có thể biết các ô của mình có được định dạng tùy chỉnh hay không bằng cách nhìn vào thanh địa chỉ (các số không sẽ hiển thị trong ô không có trong thanh địa chỉ).
Để loại bỏ các số không có tiền tố, hãy chọn các ô có các số 0 ở đầu, nhấp vào hộp ‘Định dạng Số’ và chọn tùy chọn định dạng ‘Chung’ hoặc ‘Số’.
Bây giờ, các số 0 ở đầu đã biến mất:
Xóa Zeros hàng đầu bằng cách chuyển đổi văn bản thành số
Nếu các số 0 ở đầu của bạn đã được thêm bằng cách thay đổi định dạng ô hoặc bằng cách thêm dấu nháy đơn trước các số hoặc được thêm tự động khi nhập dữ liệu, thì cách dễ nhất để chuyển chúng thành số là sử dụng Tùy chọn Kiểm tra Lỗi. Đây là cách bạn thực hiện:
Bạn có thể sử dụng phương pháp này nếu các số của bạn được căn trái và các ô của bạn có một hình tam giác nhỏ màu xanh lục (Chỉ báo lỗi) ở góc trên cùng bên trái của các ô. Điều này có nghĩa là các số được định dạng dưới dạng văn bản.
Chọn những ô đó và bạn sẽ thấy một cảnh báo màu vàng ở trên cùng bên phải của vùng chọn. Sau đó, nhấp vào tùy chọn 'Chuyển đổi thành số' từ menu thả xuống.
Các số không của bạn sẽ bị xóa và các số sẽ được chuyển đổi trở lại định dạng số (Căn phải).
Loại bỏ các Zeros hàng đầu bằng cách Nhân / Chia cho 1
Một cách dễ dàng và tốt nhất khác để loại bỏ số đứng đầu là nhân hoặc chia các số với 1. Việc chia hoặc nhân giá trị không làm thay đổi giá trị, nó chỉ chuyển đổi giá trị trở lại một số và loại bỏ các số không ở đầu.
Để thực hiện việc này, hãy nhập công thức trong ví dụ dưới đây vào một ô và nhấn ENTER. Các số 0 ở đầu sẽ bị loại bỏ và chuỗi sẽ được chuyển đổi lại thành một số.
Sau đó, áp dụng công thức này cho các ô khác bằng cách sử dụng chốt điền.
Bạn có thể đạt được kết quả tương tự bằng cách sử dụng Lệnh ‘Dán Đặc biệt’. Đây là cách thực hiện:
Nhập giá trị số ‘1’ vào một ô (giả sử trong B2) và sao chép giá trị đó.
Tiếp theo, chọn các ô mà bạn muốn loại bỏ các số không ở đầu. Sau đó, nhấp chuột phải vào vùng chọn rồi chọn tùy chọn ‘Dán đặc biệt’.
Trong hộp thoại Dán Đặc biệt, bên dưới Thao tác, hãy chọn tùy chọn ‘Nhân’ hoặc ‘Chia’ và nhấp vào ‘OK’.
Vậy là xong, các số 0 ở đầu của bạn sẽ bị xóa để lại các chuỗi dưới dạng số.
Loại bỏ các Zeros hàng đầu bằng cách sử dụng Công thức
Một cách dễ dàng khác để xóa các số không có tiền tố là sử dụng hàm VALUE. Phương pháp này có thể hữu ích cho dù các số 0 ở đầu của bạn được thêm vào bằng cách sử dụng một công thức hoặc dấu nháy đơn khác hoặc bằng cách định dạng tùy chỉnh.
= VALUE (A1)Đối số của công thức có thể là một giá trị hoặc tham chiếu ô có giá trị. Công thức loại bỏ các số không ở đầu và chuyển đổi giá trị từ văn bản thành số. Sau đó, áp dụng công thức cho các ô còn lại.
Đôi khi, bạn có thể muốn loại bỏ các số không ở đầu nhưng muốn giữ các số ở định dạng văn bản. Trong những trường hợp như vậy, bạn phải sử dụng các hàm TEXT () và VALUE () cùng nhau như sau:
= TEXT (VALUE (A1), "#")Hàm VALUE chuyển đổi giá trị trong A1 thành số. Nhưng đối số thứ hai, ‘#’ chuyển đổi giá trị trở lại định dạng văn bản mà không có thêm số 0 nào. Kết quả là, bạn sẽ nhận được các số không có bất kỳ số 0 nào ở đầu nhưng vẫn ở định dạng văn bản (căn trái).
Loại bỏ các Zeros hàng đầu bằng cách sử dụng Tính năng chuyển văn bản thành cột của Excel
Tuy nhiên, một cách khác để loại bỏ các số không ở đầu là sử dụng tính năng Text to Columns của Excel.
Chọn phạm vi ô có các số với các số không ở đầu.
Tiếp theo, chuyển đến tab ‘Dữ liệu’ và nhấp vào nút ‘Văn bản thành cột’ trong nhóm Công cụ dữ liệu.
Trình hướng dẫn 'Chuyển Văn bản thành Cột' sẽ xuất hiện. Ở bước 1/3, chọn ‘Delimited’ và nhấp vào ‘Next’.
Ở bước 2/3, bỏ chọn tất cả dấu phân cách và nhấp vào 'Tiếp theo'.
Ở bước cuối cùng, hãy để tùy chọn Định dạng dữ liệu cột là 'Chung' và chọn đích (ô đầu tiên của phạm vi) nơi bạn muốn các số của mình không có số 0 ở đầu. Sau đó, nhấp vào 'Hoàn tất'
Và bạn sẽ nhận được các số có đầu bị loại bỏ trong một cột riêng biệt như hình dưới đây.
Đó là tất cả.