Làm thế nào để loại bỏ các Zeros hàng đầu trong Excel [5 Cách dễ dàng]

Nhiều người có mối quan hệ yêu-ghét với các số không ở đầu trong Excel.Đôi khi bạn muốn nó, đôi khi bạn không.

Mặc dù Excel được lập trình để tự động loại bỏ bất kỳ số XNUMX ở đầu nào khỏi các số, nhưng có một số tình huống nhất định mà bạn có thể gặp phải những tình huống này.

Trong hướng dẫn Excel này, tôi sẽ chỉ cho bạnLàm saoLoại bỏ các số không ở đầu các số trong Excel.

Vậy hãy bắt đầu!

Nguyên nhân có thể gây ra các số XNUMX ở đầu trong Excel

Như tôi đã đề cập, Excel sẽ tự động loại bỏ tất cả các số không ở đầu các số.Ví dụ: nếu bạn nhập 00100 vào một ô trong Excel, nó sẽ tự động chuyển nó thành 100.

Trong hầu hết các trường hợp, điều này có ý nghĩa, vì những số XNUMX ở đầu đó không thực sự có ý nghĩa.

Nhưng trong một số trường hợp, bạn có thể cần nó.

Dưới đây là một số lý do tại sao các số của bạn có thể giữ lại các số XNUMX ở đầu:

• Nếu số đã được định dạng dưới dạng văn bản (chủ yếu bằng cách thêm dấu nháy đơn trước số), nó sẽ giữ nguyên các số không ở đầu.
• Các ô có thể đã được định dạng để luôn hiển thị các số có độ dài nhất định.Nếu số nhỏ hơn, các số XNUMX ở đầu sẽ được thêm vào để bù cho số đó.Ví dụ: bạn có thể định dạng ô để luôn hiển thị 5 chữ số (nếu số nhỏ hơn 5 chữ số, các số XNUMX ở đầu sẽ tự động được thêm vào)

Phương pháp chúng tôi chọn để loại bỏ các số XNUMX ở đầu phụ thuộc vào nguyên nhân gây ra nó.

Vì vậy bước đầu tiên chúng ta phải xác định được nguyên nhân để có thể lựa chọn cách loại bỏ những số XNUMX đứng đầu này phù hợp.

Cách xóa các số không ở đầu khỏi số

Bạn có thể xóa các số không ở đầu các số theo một số cách.

Trong phần này, tôi sẽ chỉ cho bạn năm phương pháp như vậy.

Chuyển văn bản thành số với các tùy chọn kiểm tra lỗi

Nếu lý do cho các số đứng đầu là do ai đó đã thêm dấu nháy đơn trước chúng (chuyển những số đó thành văn bản), bạn có thể sử dụng tính năng kiểm tra lỗi để chuyển những số đó trở lại thành số chỉ bằng một cú nhấp chuột.

Đây có lẽ là cách dễ nhất để loại bỏ các số XNUMX ở đầu.

Ở đây tôi có một tập dữ liệu với các số có dấu nháy đơn trước các số này và các số XNUMX ở đầu.Đó là lý do tại sao bạn thấy các số được căn trái (trong khi các số mặc định được căn phải) và cũng có các số 0 ở đầu.

Dưới đây là các bước để xóa các số XNUMX ở đầu này khỏi các số này:

1. Chọn số để loại bỏ các số không ở đầu.Bạn sẽ nhận thấy một biểu tượng màu vàng ở góc trên bên phải của vùng chọn.
2. Nhấp vào biểu tượng kiểm tra lỗi màu vàng
3. Nhấp vào "Chuyển đổi thành số"

Đó là nó!Các bước trên sẽloại bỏ dấu nháy đơnvà chuyển đổi các giá trị văn bản này trở lại thành số.

Và vì Excel được lập trình để xóa các dấu cách ở đầu khỏi bất kỳ số nào theo mặc định, bạn sẽ thấy rằng làm như vậy sẽ tự động loại bỏ tất cả các số XNUMX ở đầu.

Lưu ý: Phương pháp này sẽ không hoạt động nếu các số không ở đầu đã được thêm vào như một phần của định dạng số tùy chỉnh của ô.Để xử lý những tình huống này, hãy sử dụng các phương pháp được mô tả tiếp theo.

Thay đổi định dạng số tùy chỉnh của ô

Một lý do thực sự phổ biến khác khiến các số của bạn có thể hiển thị các số XNUMX ở đầu là khi các ô của bạn đã được định dạng để luôn hiển thị một số lượng nhất định trong mỗi số.

Nhiều người muốn các số trông nhất quán và có cùng độ dài, vì vậy họ chỉ định độ dài tối thiểu cho các số bằng cách thay đổi định dạng của các ô.

Ví dụ: nếu bạn muốn tất cả các số xuất hiện dưới dạng 5 chữ số, nếu bạn có một số chỉ có ba chữ số, Excel sẽ tự động thêm hai số không ở đầu vào đó.

Dưới đây, tôi có một tập dữ liệu với định dạng số tùy chỉnh được áp dụng để luôn hiển thị ít nhất năm chữ số trong một ô.

Cách để loại bỏ những số XNUMX ở đầu này chỉ đơn giản là xóa định dạng hiện có khỏi ô.

Dưới đây là các bước để thực hiện việc này:

1. Chọn các ô có số XNUMX đứng đầu
2. Nhấp vào tab Trang chủ
3. Trong nhóm Số, bấm vào menu thả xuống Định dạng Số
4. Chọn "Chung"

Các bước trên sẽ thay đổi định dạng số tùy chỉnh của ô và bây giờ các số sẽ xuất hiện như mong đợi (không có số XNUMX ở đầu trong chúng).

Lưu ý rằng kỹ thuật này chỉ hoạt động nếu lý do cho số XNUMX ở đầu là định dạng số tùy chỉnh.Nó sẽ không hoạt động nếu bạn sử dụng dấu nháy đơn để chuyển đổi số thành văn bản (trong trường hợp này, bạn nên sử dụng phương pháp trước đó)

Nhân với 1 (sử dụng kỹ thuật dán đặc biệt)

Kỹ thuật này hoạt động trong cả hai trường hợp (khi số đã được chuyển đổi thành văn bản bằng cách sử dụng dấu nháy đơn hoặc khi định dạng số tùy chỉnh đã được áp dụng cho ô).

Giả sử bạn có một tập dữ liệu như bên dưới và bạn muốn xóa các số XNUMX ở đầu khỏi nó.

Đây là các bước để làm điều này

1. Sao chép bất kỳ ô trống nào trong trang tính
2. Chọn các ô có số mà bạn muốn xóa các số không ở đầu
3. Nhấp chuột phải vào lựa chọn và nhấp vào Dán đặc biệt.Thao tác này sẽ mở hộp thoại Dán Đặc biệt
4. Nhấp vào tùy chọn Thêm (trong nhóm Hành động)
5. Bấm OK

Các bước trên thêm 0 vào phạm vi ô đã chọn của bạn và xóa tất cả các số XNUMX ở đầu và dấu nháy đơn.

Mặc dù điều này không thay đổi giá trị của ô, nhưng nó sẽ chuyển đổi tất cả các giá trị văn bản thành số và sao chép định dạng từ ô trống mà bạn đã sao chép (do đó thay thế định dạng hiện tại làm xuất hiện các số không ở đầu).

Phương pháp này chỉ ảnh hưởng đến các con số.Nếu có bất kỳ chuỗi văn bản nào trong ô, nó sẽ không thay đổi.

Sử dụng hàm VALUE

Một cách nhanh chóng và dễ dàng khác để loại bỏ các số XNUMX ở đầu là sử dụng một hàm giá trị.

Hàm này nhận một đối số (có thể là văn bản hoặc tham chiếu ô có văn bản) và trả về một giá trị số.

Điều này cũng áp dụng cho cả hai trường hợp số đứng đầu là dấu nháy đơn (được sử dụng để chuyển đổi số thành văn bản) hoặc kết quả của định dạng số tùy chỉnh.

Giả sử tôi có một tập dữ liệu giống như sau:

Đây là công thức để loại bỏ các số không ở đầu:

= VALUE (A1)

Lưu ý: Nếu bạn vẫn thấy các số XNUMX ở đầu, bạn cần chuyển đến tab Trang đầu và thay đổi định dạng ô thành Chung (từ trình đơn thả xuống Định dạng Số).

Sử dụng văn bản thành cột

Trong khi tính năng Văn bản thành Cột được sử dụng để chia ô thành nhiều cột, bạn cũng có thể sử dụng tính năng này để loại bỏ các số không ở đầu.

Giả sử bạn có một tập dữ liệu giống như sau:

Dưới đây là các bước để loại bỏ các số không ở đầu bằng cách sử dụng Văn bản thành Cột:

1. Chọn dải ô có số
2. Nhấp vào tab Dữ liệu
3. Trong nhóm Công cụ dữ liệu, hãy bấm Văn bản thành Cột
4. Trong trình hướng dẫn Chuyển Văn bản thành Cột, hãy thực hiện các thay đổi sau:
   1. Bước 1/3: Chọn "Tách biệt" và nhấp vào "Tiếp theo"
   2. Bước 2/3: Bỏ chọn tất cả các dấu phân cách và nhấp vào Tiếp theo
   3. Bước 3/3: Chọn một ô đích (trong trường hợp này là B2) và nhấp vào Kết thúc

Các bước trên sẽ loại bỏ tất cả các số XNUMX ở đầu và chỉ cung cấp các số.Nếu bạn vẫn thấy các số XNUMX ở đầu, bạn cần thay đổi định dạng của ô thành Chung (điều này có thể được thực hiện từ tab Trang chủ)

Cách xóa các số không ở đầu văn bản

Mặc dù tất cả các phương pháp trên đều hoạt động tốt, nhưng các phương pháp này chỉ hoạt động đối với những ô có giá trị số.

Nhưng điều gì sẽ xảy ra nếu các giá trị chữ và số hoặc văn bản của bạn cũng có một số số XNUMX ở đầu.

Trong trường hợp này, phương pháp trên sẽ không hoạt động, nhưng nhờ các công thức tuyệt vời trong Excel, bạn vẫn có thể nắm bắt được thời gian.

Giả sử bạn có một tập dữ liệu như sau và bạn muốn xóa tất cả các số XNUMX ở đầu khỏi nó:

Đây là công thức để làm điều này:

=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)

Hãy để tôi giải thích cách hoạt động của công thức này

Phần SUBSTITUTE của công thức thay thế các số không bằng các khoảng trống.Vì vậy, đối với giá trị 001AN76, công thức thay thế cho kết quả 1AN76

Sau đó, công thức LEFT trích xuất ký tự ngoài cùng bên trái của chuỗi kết quả này, trong trường hợp này là 1.

Sau đó, công thức FIND sẽ tìm ký tự ngoài cùng bên trái được cung cấp bởi công thức LEFT và trả về vị trí của nó.Trong ví dụ của chúng tôi, đối với giá trị 001AN76, nó sẽ cho 3 (nghĩa là vị trí của 1 trong chuỗi văn bản gốc).

1 được thêm vào kết quả của công thức TÌM để đảm bảo chúng tôi đã trích xuất toàn bộ chuỗi văn bản (ngoại trừ các số XNUMX ở đầu)

Sau đó, trừ kết quả của công thức FIND với kết quả của công thức LEN, cho độ dài của toàn bộ chuỗi văn bản.Điều này cho chúng ta độ dài của vòng lặp văn bản mà không có số XNUMX ở đầu.

Giá trị này sau đó được sử dụng với hàm RIGHT để trích xuất toàn bộ chuỗi văn bản (ngoại trừ các số XNUMX ở đầu).

Nếu bạn có thể có khoảng trống ở đầu hoặc cuối trong các ô của mình, tốt nhất bạn nên sử dụng hàm TRIM cho mỗi tham chiếu ô.

Do đó, công thức mới với việc bổ sung hàm TRIM trông giống như sau:

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(LEFT(SUBSTITUTE(TRIM(A2),"0",""),1),TRIM(A2))+1)

Do đó, bạn có thể sử dụng các phương pháp đơn giản này để loại bỏ các số XNUMX ở đầu khỏi tập dữ liệu trong Excel.

Tôi hy vọng bạn thấy hướng dẫn này hữu ích!