Hướng dẫn thể hiện một vài cách nhanh chóng và dễ dàng để cắt không gian Excel. Tìm hiểu làm thế nào để loại bỏ các không gian dẫn đầu, dấu vết và thêm không gian giữa các từ, tại sao chức năng trang trí Excel không hoạt động và cách khắc phục nó.
Bạn đang so sánh hai cột cho các bản sao mà bạn biết là có, nhưng các công thức của bạn không thể tìm thấy một mục trùng lặp duy nhất? Hoặc, bạn đang thêm hai cột số, nhưng cứ chỉ nhận được số không? Và tại sao trên trái đất, công thức vlookup chính xác của bạn trở lại chỉ là một loạt các lỗi N/A? Đây chỉ là một vài ví dụ về các vấn đề mà bạn có thể đang tìm kiếm câu trả lời. Và tất cả đều được gây ra bởi các không gian bổ sung ẩn trước, sau hoặc giữa các giá trị số và văn bản trong các ô của bạn.extra spaces hiding before, after or between numeric and text values in your cells.
Microsoft Excel cung cấp một vài cách khác nhau để xóa không gian và dọn dẹp dữ liệu của bạn. Trong hướng dẫn này, chúng tôi sẽ điều tra các khả năng của chức năng trang trí là cách nhanh nhất và dễ nhất để xóa không gian trong Excel.
Chức năng cắt - Xóa các khoảng trống bổ sung trong Excel
Bạn sử dụng chức năng trang trí trong Excel sẽ loại bỏ các không gian bổ sung khỏi văn bản. Nó xóa tất cả các không gian dẫn đầu, kéo dài và giữa các không gian ngoại trừ một ký tự không gian duy nhất giữa các từ.except for a single space character between words.
Cú pháp của hàm trang trí là dễ nhất có thể tưởng tượng:
TRIM[text]
Trong đó văn bản là một ô mà bạn muốn loại bỏ các không gian dư thừa.text is a cell that you want to remove excess spaces from.
Ví dụ: để loại bỏ các khoảng trống trong ô A1, bạn sử dụng công thức này:
=TRIM[A1]
Và ảnh chụp màn hình sau đây cho thấy kết quả:
Đúng, thật đơn giản!
Xin lưu ý rằng hàm trang trí được thiết kế để chỉ loại bỏ ký tự không gian, có giá trị 32 trong hệ thống mã ASCII 7 bit. Nếu ngoài các không gian bổ sung, dữ liệu của bạn chứa các lỗi đường và các ký tự không in, hãy sử dụng hàm trang trí kết hợp với Clean để xóa 32 ký tự không in đầu tiên trong hệ thống ASCII.
Ví dụ: để xóa không gian, ngắt dòng và các ký tự không mong muốn khác khỏi Cell A1, hãy sử dụng công thức này:
=TRIM[CLEAN[A1]]
Để biết thêm thông tin, vui lòng xem cách xóa các ký tự không in trong Excel
Để loại bỏ các không gian không phá vỡ [ký tự HTML & NBSP;], có giá trị 160, sử dụng trang trí cùng với các hàm thay thế và Char:
=TRIM[SUBSTITUTE[A1, CHAR[160], " "]]
Để biết chi tiết đầy đủ, vui lòng xem cách xóa không gian không phá vỡ trong Excel
Cách sử dụng chức năng trang trí trong Excel - Ví dụ công thức
Bây giờ bạn đã biết những điều cơ bản, chúng ta hãy thảo luận về một vài cách sử dụng cụ thể của trang trí trong Excel, những cạm bẫy mà bạn có thể phải đối mặt và các giải pháp làm việc.
Cách cắt không gian trong toàn bộ cột dữ liệu
Giả sử bạn có một cột tên có một số khoảng trắng trước và sau văn bản, cũng như nhiều khoảng trắng giữa các từ. Vì vậy, làm thế nào để bạn loại bỏ tất cả các không gian dẫn đầu, kéo dài và dư thừa trong tất cả các ô tại một thời điểm? Bằng cách sao chép một công thức trang trí excel trên cột, sau đó thay thế các công thức bằng các giá trị của chúng. Các bước chi tiết theo dưới đây.
- Viết một công thức trang trí cho ô trên cùng, A2 trong ví dụ của chúng tôi:
=TRIM[A2] - Đặt con trỏ vào góc dưới bên phải của ô công thức [B2 trong ví dụ này] và ngay khi con trỏ biến thành dấu cộng, nhấp đúp vào nó để sao chép công thức xuống cột, lên đến ô cuối cùng với dữ liệu . Kết quả là, bạn sẽ có 2 cột - tên gốc với khoảng trắng và tên được cắt theo công thức.
- Cuối cùng, thay thế các giá trị trong cột gốc bằng dữ liệu được cắt. Nhưng hãy cẩn thận! Chỉ cần sao chép cột được cắt ngang qua cột ban đầu sẽ phá hủy các công thức của bạn. Để ngăn chặn điều này xảy ra, bạn chỉ cần sao chép các giá trị, không phải công thức. Đây là cách:
- Chọn tất cả các ô có công thức trang trí [B2: B8 trong ví dụ này] và nhấn Ctrl+C để sao chép chúng.Ctrl+C to copy them.
- Chọn tất cả các ô có dữ liệu gốc [A2: A8] và nhấn Ctrl+Alt+V, sau đó V. Đó là phím tắt giá trị dán áp dụng các giá trị đặc biệt>Ctrl+Alt+V, then V. It is the paste values shortcut that applies the Paste Special > Values
- Nhấn phím Enter. Xong!Enter key. Done!
Cách loại bỏ các không gian hàng đầu trong một cột số
Như bạn vừa thấy, chức năng trang trí Excel đã loại bỏ tất cả các không gian bổ sung khỏi một cột dữ liệu văn bản mà không gặp trở ngại. Nhưng điều gì sẽ xảy ra nếu dữ liệu của bạn là số, không phải văn bản?
Thoạt nhìn, có vẻ như chức năng trang trí đã hoàn thành công việc của mình. Tuy nhiên, khi xem xét kỹ hơn, bạn sẽ nhận thấy rằng các giá trị được cắt không hoạt động như số. Đây chỉ là một vài dấu hiệu của sự bất thường:
- Cả cột ban đầu với các không gian hàng đầu và số được cắt được liên kết trái ngay cả khi bạn áp dụng định dạng số cho các ô, trong khi các số bình thường được liên kết bên phải theo mặc định.
- Khi hai hoặc nhiều ô có số được cắt được chọn, Excel chỉ hiển thị được tính trong thanh trạng thái. Đối với các số, nó cũng nên hiển thị tổng và trung bình.
- Một công thức tổng được áp dụng cho các ô được cắt giảm không.
Từ tất cả các lần xuất hiện, các giá trị được cắt là các chuỗi văn bản, trong khi chúng tôi muốn số. Để khắc phục điều này, bạn có thể nhân các giá trị được cắt với 1 [để nhân tất cả các giá trị trong một cú đánh rơi, sử dụng tùy chọn Paste Special> Nhân].text strings, while we want numbers. To fix this, you can multiply the trimmed values by 1 [to multiply all the values in one fell swoop, use the Paste Special > Multiply option].
Một giải pháp thanh lịch hơn đang kèm theo hàm trang trí về giá trị, như thế này:
=VALUE[TRIM[A2]]
Công thức trên sẽ loại bỏ tất cả các không gian dẫn và dấu vết, nếu có, và biến giá trị kết quả thành một số, như thể hiện trong ảnh chụp màn hình bên dưới:
Cách chỉ loại bỏ các không gian hàng đầu trong Excel [trang trí bên trái]
Trong một số tình huống, bạn có thể nhập các không gian trùng lặp và thậm chí ba lần giữa các từ để làm cho dữ liệu của bạn dễ đọc hơn. Tuy nhiên, bạn muốn thoát khỏi không gian hàng đầu, như thế này:
Như bạn đã biết, chức năng trang trí loại bỏ các không gian bổ sung ở giữa các chuỗi văn bản, đó không phải là điều chúng ta muốn. Để giữ cho tất cả các khoảng trống ở giữa, chúng tôi sẽ sử dụng một công thức phức tạp hơn một chút:
=MID[A2,FIND[MID[TRIM[A2],1,1],A2],LEN[A2]]
Trong công thức trên, sự kết hợp của tìm, mid và trang trí tính toán vị trí của ký tự văn bản đầu tiên trong một chuỗi. Và sau đó, bạn cung cấp số đó cho một hàm giữa khác để nó trả về toàn bộ chuỗi văn bản [độ dài chuỗi được tính bằng LEN] bắt đầu ở vị trí của ký tự văn bản đầu tiên.
Ảnh chụp màn hình sau đây cho thấy tất cả các không gian hàng đầu đã biến mất, trong khi nhiều khoảng trống giữa các từ vẫn còn đó:
Như một liên lạc hoàn thiện, thay thế văn bản gốc bằng các giá trị được cắt, như thể hiện trong bước 3 của ví dụ Công thức trang trí và bạn rất tốt để đi!
Mẹo. Nếu bạn cũng muốn loại bỏ các khoảng trống từ cuối ô, hãy sử dụng công cụ không gian trang trí. Không có công thức Excel rõ ràng để loại bỏ các không gian dẫn đầu và dấu vết giữ nhiều khoảng trống giữa các từ còn nguyên vẹn. If you also want to remove spaces from the end of cells, make use of the Trim Spaces tool. There is no obvious Excel formula to remove leading and trailing spaces keeping multiple spaces between words intact.
Cách đếm thêm không gian trong ô
Đôi khi, trước khi loại bỏ khoảng trống trong bảng excel của bạn, bạn có thể muốn biết có bao nhiêu không gian dư thừa thực sự ở đó.
Để có được số lượng không gian bổ sung trong một ô, hãy tìm ra tổng chiều dài văn bản bằng hàm LEN, sau đó tính độ dài chuỗi mà không cần thêm khoảng trắng và trừ cái sau khỏi cái trước:
=LEN[A2]-LEN[TRIM[A2]]
Ảnh chụp màn hình sau đây cho thấy công thức trên trong hành động:
Ghi chú. Công thức trả về số lượng không gian bổ sung trong một ô, tức là dẫn đầu, theo dõi và nhiều hơn một không gian liên tiếp giữa các từ, nhưng nó không đếm được một không gian ở giữa văn bản. Nếu bạn muốn có được tổng số không gian trong một ô, hãy sử dụng công thức thay thế này. The formula returns the count of extra spaces in a cell, i.e. leading, trailing, and more than one consecutive spaces between words, but it does not count single spaces in the middle of the text. If you want to get the total number of spaces in a cell, use this Substitute formula.
Cách làm nổi bật các tế bào với không gian dư thừa
Khi làm việc với thông tin nhạy cảm hoặc quan trọng, bạn có thể ngần ngại xóa bất cứ điều gì mà không thấy chính xác những gì bạn đang xóa. Trong trường hợp này, bạn có thể làm nổi bật các ô có chứa thêm không gian trước, và sau đó loại bỏ các không gian đó một cách an toàn.
Đối với điều này, hãy tạo một quy tắc định dạng có điều kiện với công thức sau:
=LEN[$A2]>LEN[TRIM[$A2]]
Trong đó A2 là ô trên cùng với dữ liệu mà bạn muốn làm nổi bật.
Công thức hướng dẫn Excel làm nổi bật các ô trong đó tổng chiều dài chuỗi lớn hơn chiều dài của văn bản được cắt.
Để tạo quy tắc định dạng có điều kiện, hãy chọn tất cả các ô [hàng] mà bạn muốn làm nổi bật mà không cần tiêu đề cột, hãy truy cập nhóm Tab Home> Styles và nhấp vào định dạng có điều kiện> Quy tắc mới> Sử dụng công thức để xác định định dạng ô nào.
Nếu bạn chưa quen thuộc với định dạng có điều kiện Excel, bạn sẽ tìm thấy các bước chi tiết ở đây: Cách tạo quy tắc định dạng có điều kiện dựa trên công thức.
Như đã được chứng minh trong ảnh chụp màn hình bên dưới, kết quả hoàn toàn chứng thực với số lượng không gian bổ sung mà chúng ta có trong ví dụ trước:
Như bạn thấy, việc sử dụng hàm trang trí trong Excel rất dễ dàng và đơn giản. Tuy nhiên, nếu ai đó muốn xem xét kỹ hơn các công thức được thảo luận trong hướng dẫn này, bạn có thể tải xuống Sổ làm việc không gian Excel Trim Excel.
Excel Trim không hoạt động
Hàm trang trí chỉ loại bỏ ký tự không gian được biểu thị bằng giá trị mã 32 trong bộ ký tự ASCII 7 bit. Trong bộ ký tự Unicode, có thêm một ký tự không gian được gọi là không gian không phá vỡ, thường được sử dụng trên các trang web làm ký tự HTML & nbsp ;. Không gian không phá vỡ có giá trị thập phân là 160 và hàm trang trí không thể tự xóa nó.space character represented by code value 32 in the 7-bit ASCII character set. In the Unicode character set, there is one more space character called the non-breaking space, which is commonly used on web pages as the html character . The nonbreaking space has a decimal value of 160, and the TRIM function cannot remove it by itself.
Vì vậy, nếu bộ dữ liệu của bạn chứa một hoặc nhiều không gian trắng mà hàm trang trí không xóa, hãy sử dụng hàm thay thế để chuyển đổi không gian không phá vỡ thành các không gian thông thường và sau đó cắt chúng. Giả sử văn bản nằm trong A1, công thức diễn ra như sau:
=TRIM[SUBSTITUTE[A1, CHAR[160], " "]]
Để có biện pháp phòng ngừa thêm, bạn có thể nhúng chức năng sạch để làm sạch ô của bất kỳ ký tự không thể in nào:
=TRIM[CLEAN[SUBSTITUTE[A1, CHAR[160], " "]]]
Ảnh chụp màn hình sau đây cho thấy sự khác biệt:
Nếu các công thức trên cũng không phù hợp với bạn, rất có thể dữ liệu của bạn chứa một số ký tự không rõ ràng cụ thể với các giá trị mã không phải là 32 và 160. Trong trường hợp này, hãy sử dụng một trong các công thức sau để tìm ra mã ký tự Một tế bào có vấn đề:
Không gian hàng đầu: =TRIM[CLEAN[A1]]0
Không gian theo dõi: =TRIM[CLEAN[A1]]1
Ở giữa không gian [trong đó n là vị trí của ký tự có vấn đề trong chuỗi văn bản]:
=TRIM[CLEAN[A1]]2
Và sau đó, cung cấp mã ký tự được trả về cho công thức Trim [thay thế []] được thảo luận ở trên.
Ví dụ: nếu hàm mã trả về 9, đó là ký tự tab ngang, bạn sẽ sử dụng công thức sau để xóa nó:
=TRIM[CLEAN[A1]]3
Cắt không gian cho Excel - Xóa thêm không gian bằng cách nhấp chuột
Có phải ý tưởng học một số ít các công thức khác nhau để đối phó với một nhiệm vụ tầm thường nghe có vẻ lố bịch? Sau đó, bạn có thể thích kỹ thuật một cú nhấp chuột này để thoát khỏi không gian trong Excel. Hãy để tôi giới thiệu với bạn về bộ công cụ văn bản được bao gồm trong bộ cuối cùng của chúng tôi. Trong số những thứ khác như thay đổi trường hợp, phân tách văn bản và định dạng xóa, nó cung cấp tùy chọn không gian trang trí.Trim Spaces option.
Với Ultimate Suite được cài đặt trong Excel của bạn, việc loại bỏ các khoảng trống trong Excel cũng đơn giản như thế này:
- Chọn [các] ô nơi bạn muốn xóa không gian.
- Nhấp vào nút Trim Spaces trên ruy băng.
- Chọn một hoặc tất cả các tùy chọn sau:
- Cắt không gian hàng đầu và dấu vếtleading and trailing spaces
- Cắt tỉa các phần tử giữa các từ, ngoại trừ một khoảng trốngextra spaces between words, except for a single space
- Cắt không gian không phá vỡ [& NBSP;]non-breaking spaces [ ]
- Nhấp vào Trim.
Thats tất cả để có nó! Tất cả các không gian bổ sung được loại bỏ trong nháy mắt.
Trong ví dụ này, chúng tôi chỉ loại bỏ các không gian dẫn đầu và theo dõi, giữ nhiều khoảng trống giữa các từ còn nguyên vẹn để dễ đọc hơn - nhiệm vụ mà các công thức Excel không thể đối phó được được thực hiện bằng một cú nhấp chuột của chuột!
Nếu bạn quan tâm để thử trang trí không gian trong bảng của mình, bạn có thể tải xuống phiên bản đánh giá vào cuối bài đăng này.
Tôi cảm ơn bạn đã đọc và mong được gặp bạn vào tuần tới. Trong hướng dẫn tiếp theo của chúng tôi, chúng tôi sẽ thảo luận về các cách khác để cắt không gian ở Excel, xin vui lòng theo dõi!
Tải xuống có sẵn
Trang trí không gian Excel - Ví dụ công thức [tệp .xlsx] Ultimate Suite - Phiên bản dùng thử [tệp .exe]
Ultimate Suite - trial version [.exe file]