Google Trang tính: xóa cùng một văn bản hoặc một số ký tự nhất định khỏi nhiều ô cùng một lúc

Tìm hiểu công thức và các cách không có công thức để cắt bỏ khoảng trắng, xóa các ký hiệu đặc biệt (thậm chí N ký tự đầu tiên / cuối cùng) và các chuỗi văn bản giống nhau trước / sau các ký tự nhất định từ nhiều ô cùng một lúc trong Excel.

Việc xóa cùng một phần văn bản khỏi nhiều ô cùng một lúc có thể quan trọng và phức tạp như việc thêm nó. Ngay cả khi bạn biết một số cách, bạn chắc chắn sẽ tìm thấy những cách mới trong bài đăng blog hôm nay. Tôi chia sẻ nhiều hàm và các công thức làm sẵn của chúng và như mọi khi, tôi tiết kiệm công thức dễ dàng nhất – không có công thức – cuối cùng;)

Công thức cho Google Trang tính để xóa văn bản khỏi ô

Tôi sẽ bắt đầu với các hàm tiêu chuẩn cho Google Trang tính sẽ xóa các chuỗi văn bản và ký tự của bạn khỏi các ô. Không có hàm phổ quát cho việc này, vì vậy tôi sẽ cung cấp các công thức khác nhau và sự kết hợp của chúng cho các trường hợp khác nhau.

Google Trang tính: xóa khoảng trắng

Khoảng trắng có thể dễ dàng trượt vào các ô sau khi nhập hoặc nếu nhiều người dùng chỉnh sửa trang tính cùng một lúc. Trên thực tế, các khoảng trắng thừa phổ biến đến mức Google Trang tính có một công cụ Trim đặc biệt để loại bỏ tất cả các khoảng trắng.

Chỉ cần chọn tất cả các ô Google Trang tính mà bạn muốn xóa khoảng trắng và chọn Dữ liệu> Cắt bỏ khoảng trắng trong menu bảng tính:

Find the Trim whitespace tool in the Google Sheets menu.
Khi bạn nhấp vào tùy chọn, tất cả các khoảng trắng ở đầu và cuối trong vùng chọn sẽ bị xóa hoàn toàn trong khi tất cả các khoảng trắng thừa ở giữa dữ liệu sẽ được giảm xuống một:
How Google Sheets removes whitespaces.

Xóa các ký tự đặc biệt khác khỏi chuỗi văn bản trong Google Trang tính

Rất tiếc, Google Trang tính không cung cấp công cụ để ‘cắt’ các ký tự khác ngoài khoảng trắng. Bạn phải đối phó với các công thức ở đây.

Tiền boa. Hoặc sử dụng công cụ của chúng tôi thay thế – Power Tools sẽ giải phóng phạm vi của bạn khỏi bất kỳ ký tự nào bạn chỉ định trong một cú nhấp chuột.

Ở đây tôi đã giải quyết bằng các thẻ bắt đầu bằng # trước số căn hộ và số điện thoại có dấu gạch ngang và dấu ngoặc ở giữa:

Sample data with various special characters.

Tôi sẽ sử dụng công thức để loại bỏ các ký tự đặc biệt đó.

Hàm SUBSTITUTE sẽ giúp tôi điều đó. Nó thường được sử dụng để thay thế một ký tự này bằng một ký tự khác, nhưng bạn có thể biến điều đó thành lợi thế của mình và thay thế các ký tự không mong muốn bằng… tốt, không có gì 🙂 Nói cách khác, hãy loại bỏ nó.

Hãy xem hàm yêu cầu đối số nào:

SUBSTITUTE (text_to_search, search_for, Replace_with, [domains_number])
  • text_to_search là văn bản để xử lý hoặc một ô chứa văn bản đó. Cần thiết.
  • search_for là ký tự mà bạn muốn tìm và xóa. Cần thiết.
  • Replace_with – một ký tự bạn sẽ chèn thay vì ký hiệu không mong muốn. Cần thiết.
  • Số lần xuất hiện – nếu có một số trường hợp của ký tự bạn đang tìm kiếm, tại đây bạn có thể chỉ định ký tự nào cần thay thế. Nó hoàn toàn là tùy chọn và nếu bạn bỏ qua đối số này, tất cả các phiên bản sẽ được thay thế bằng một cái gì đó mới ( Replace_for ).

Và cùng chơi nào. Tôi cần tìm một thẻ bắt đầu bằng # ( # ) trong A1 và thay thế nó bằng ‘không có gì’ được đánh dấu trong bảng tính bằng dấu ngoặc kép ( “” ). Với tất cả những điều đó, tôi có thể xây dựng công thức sau:

=SUBSTITUTE(A1,"#","")

Tiền boa. Thẻ bắt đầu bằng # cũng nằm trong dấu ngoặc kép vì đây là cách bạn nên đề cập đến chuỗi văn bản trong công thức trong Google Trang tính.

Sau đó, sao chép công thức này xuống cột nếu Google Trang tính không tự động làm điều đó và bạn sẽ nhận được địa chỉ của mình mà không có thẻ bắt đầu bằng #:

Remove hashtags from Google Sheets cells.

Nhưng còn những dấu gạch ngang và dấu ngoặc vuông đó thì sao? Bạn có nên tạo công thức bổ sung không? Không có gì! Nếu bạn lồng nhiều hàm SUBSTITUTE trong một công thức Google Trang tính, bạn sẽ xóa tất cả các ký tự này khỏi mỗi ô:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"#",""),"(",""),")",""),"-","")

Công thức này xóa từng ký tự một và mỗi SUBSTITUTE, bắt đầu từ giữa, trở thành phạm vi cần xem xét cho SUBSTITUTE tiếp theo:

Remove characters from strings in all cells in Google Sheets.

Tiền boa. Hơn nữa, bạn có thể bọc cái này trong ArrayFormula và bao phủ toàn bộ cột cùng một lúc:

=ArrayFormula(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A7,"#",""),"(",""),")",""),"-",""))

Xóa văn bản cụ thể khỏi các ô trong Google Trang tính

Mặc dù bạn có thể sử dụng hàm SUBSTITUTE đã nói ở trên cho Google Trang tính để xóa văn bản khỏi ô, nhưng tôi cũng muốn hiển thị một hàm khác – REGEXREPLACE.

Tên của nó là một từ viết tắt của ‘biểu thức chính quy thay thế’. Và tôi sẽ sử dụng các biểu thức chính quy để tìm kiếm các chuỗi cần xóa và thay thế chúng bằng  nothing’ ( “” ).

Tiền boa. Nếu bạn không quan tâm đến việc sử dụng cụm từ thông dụng, tôi sẽ mô tả một cách dễ dàng hơn nhiều ở cuối bài đăng trên blog này .
REGEXREPLACE (văn bản, biểu_thức_chính_phục, thay thế)

Như bạn có thể thấy, có ba đối số cho hàm:

  • text – là nơi bạn đang tìm kiếm chuỗi văn bản để loại bỏ. Nó có thể là chính văn bản được viết trong dấu ngoặc kép hoặc tham chiếu đến một ô / phạm vi có văn bản.
  • Regular_expression – mẫu tìm kiếm của bạn bao gồm các tổ hợp ký tự khác nhau. Bạn sẽ tìm kiếm tất cả các chuỗi phù hợp với mẫu này. Cuộc tranh cãi này là nơi mọi điều thú vị xảy ra, nếu tôi có thể nói như vậy.
  • thay thế – một chuỗi văn bản mong muốn mới.

Giả sử các ô của tôi có dữ liệu cũng chứa tên quốc gia ( Hoa Kỳ ) nếu các vị trí khác nhau trong ô:

When the same text string is in a different position in each cell.
REGEXREPLACE sẽ giúp tôi xóa nó như thế nào?

=REGEXREPLACE(A1,"(.*)US(.*)","$1 $2")

Google Sheets: remove the same text from all cells at once.

Đây là cách công thức hoạt động chính xác:

  • nó quét nội dung của ô A1
  • đối với các kết quả phù hợp với mặt nạ này: “(. *) US (. *)”Mặt nạ này cho chức năng tìm kiếm Hoa Kỳ bất kể số ký tự khác có thể đứng trước (. *) Hoặc theo sau (. *) Tên quốc gia.Và toàn bộ mặt nạ được đặt trong dấu ngoặc kép theo yêu cầu của hàm 🙂
  • đối số cuối cùng – “$ 1 $ 2” – là những gì tôi muốn lấy. $ 1 và $ 2 mỗi đại diện cho một trong 2 nhóm ký tự – (. *) – từ đối số trước đó. Bạn nên đề cập đến các nhóm đó trong đối số thứ ba theo cách này để công thức có thể trả về mọi thứ có thể đứng trước và sau Hoa KỳĐối với Mỹ chính nó, tôi chỉ đơn giản là không đề cập đến nó trong đối số thứ 3 – có nghĩa là, tôi muốn quay trở lại tất cả mọi thứ từ A1 mà không cần sự Mỹ .
Tiền boa. Có một trang đặc biệt mà bạn có thể tham khảo để tạo các biểu thức chính quy khác nhau và tìm kiếm văn bản ở các vị trí khác nhau của ô.

Tiền boa. Đối với những dấu phẩy còn lại đó, hàm SUBSTITUTE được mô tả ở trên sẽ giúp loại bỏ chúng;) Bạn thậm chí có thể bao gồm REGEXREPLACE với SUBSTITUTE và giải quyết mọi thứ bằng một công thức:

=SUBSTITUTE(REGEXREPLACE(A1,"(.*)US(.*)","$1 $2"),",","")

Xóa văn bản trước / sau các ký tự nhất định trong tất cả các ô đã chọn

Ví dụ 1. Hàm REGEXREPLACE dành cho Google Trang tính

Khi nói đến việc loại bỏ mọi thứ trước và sau một số ký tự nhất định, REGEXREPLACE cũng hữu ích. Hãy nhớ rằng, hàm yêu cầu 3 đối số:

REGEXREPLACE (văn bản, biểu_thức_chính_phục, thay thế)

Và, như tôi đã đề cập ở trên khi tôi giới thiệu hàm , đây là hàm thứ hai bạn nên sử dụng đúng cách để hàm biết những gì cần tìm và loại bỏ.

Vậy làm cách nào để xóa địa chỉ và chỉ giữ lại số điện thoại trong ô?

Delete all before a plus sign.
Đây là công thức tôi sẽ sử dụng:

=REGEXREPLACE(A1,".*n.*(+.*)","$1")

Remove text before a character.

    • Đây là biểu thức chính quy mà tôi sử dụng trong trường hợp này: “. * \ N. * (\ +. *)”Trong phần đầu tiên – . * \ N. * – Tôi sử dụng dấu gạch chéo ngược + n để cho biết rằng ô của tôi có nhiều hơn một hàng. Vì vậy, tôi muốn hàm xóa mọi thứ trước và sau khi ngắt dòng đó (bao gồm cả nó).Phần thứ hai nằm trong ngoặc (\ +. *) Nói rằng tôi muốn giữ nguyên dấu cộng và mọi thứ theo sau nó. Tôi lấy phần này trong ngoặc để nhóm lại và ghi nhớ cho sau này.
      Tiền boa. Dấu gạch chéo ngược được sử dụng trước dấu cộng để biến nó thành một ký tự mà bạn đang tìm kiếm. Nếu không có nó, dấu cộng sẽ chỉ là một phần của biểu thức đại diện cho một số ký tự khác (ví dụ như dấu hoa thị).
    • Đối với đối số cuối cùng – $ 1 – nó làm cho hàm trả về nhóm duy nhất từ ​​đối số thứ hai: dấu cộng và mọi thứ theo sau (\ +. *) .

    Theo cách tương tự, bạn có thể xóa tất cả các số điện thoại nhưng vẫn giữ các địa chỉ:

    =REGEXREPLACE(A1,"(.*\n).*","$1")

    Chỉ lần này, bạn yêu cầu hàm nhóm (và trả lại) mọi thứ trước khi ngắt dòng và xóa phần còn lại:

Google Sheets: remove text after a certain character.

Ví dụ 2. RIGHT + LEN + FIND

Có một nhóm chức năng khác của Google Trang tính cho phép bạn xóa văn bản trước một ký tự nhất định. Chúng là QUYỀN, LEN và TÌM.

Ghi chú. Cách này chỉ có thể thực hiện được nếu các bản ghi cần giữ có cùng độ dài, chẳng hạn như số điện thoại trong trường hợp của tôi. Nếu không, chỉ cần sử dụng REGEXREPLACE thay thế hoặc tốt hơn, công cụ dễ dàng hơn được mô tả ở cuối.

Sử dụng chúng theo một thứ tự cụ thể sẽ giúp tôi nhận được cùng một kết quả và xóa toàn bộ văn bản trước một ký tự – dấu cộng:

=RIGHT(A1,(LEN(A1)-(FIND("+",A1)-1)))

Erase everything before the plus sign using three Google Sheets functions together.

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

  • FIND (“+”, A1) -1 định vị số vị trí của dấu cộng trong A1 ( 24 ) và trừ đi 1 để tổng không bao gồm chính nó: 23 .
  • LEN (A1) – (FIND (“+”, A1) -1) kiểm tra tổng số ký tự trong A1 ( 40 ) và lấy nó trừ đi 23 (được tính bằng FIND): 17 .
  • Và sau đó RIGHT trả về 17 ký tự từ cuối (bên phải) của A1.

Thật không may, cách này sẽ không giúp ích nhiều để xóa văn bản sau dấu ngắt dòng trong trường hợp của tôi (xóa số điện thoại và giữ địa chỉ), vì các địa chỉ có độ dài khác nhau.

Không sao đâu. Công cụ ở cuối vẫn làm công việc này tốt hơn;)

Xóa N ký tự đầu tiên / cuối cùng khỏi chuỗi trong Google Trang tính

Bất cứ khi nào bạn cần xóa một số ký tự khác nhau ở đầu hoặc cuối ô, REGEXREPLACE và RIGHT / LEFT + LEN cũng sẽ hữu ích.

Ghi chú. Vì tôi đã giới thiệu các hàm này ở trên, nên tôi sẽ nói ngắn gọn điểm này và cung cấp một số công thức làm sẵn. Vui lòng chuyển sang giải pháp đơn giản nhất được mô tả ở phần cuối .

Vì vậy, làm thế nào tôi có thể xóa mã khỏi các số điện thoại này? Hay nói cách khác, xóa 9 ký tự đầu tiên khỏi các ô:

How to get rid of those codes in the phone numbers?

  • Sử dụng REGEXREPLACE. Tạo một biểu thức chính quy sẽ tìm và xóa mọi thứ lên đến ký tự thứ 9 (bao gồm cả ký tự thứ 9 đó):
    =REGEXREPLACE(A1,"(.{9})(.*)","$2")Remove the first 9 chars from cells using REGEXRELACE.

    • Tiền boa. Để xóa N ký tự cuối cùng, chỉ cần hoán đổi các nhóm trong biểu thức chính quy:

      =REGEXREPLACE(A1,"(.*)(.{9})","$1")

    • RIGHT / LEFT + LEN cũng đếm số ký tự cần xóa và trả lại phần còn lại từ cuối hoặc đầu ô tương ứng:=RIGHT(A1,LEN(A1)-9)
  • Remove the first 9 chars from cells using RIGHT+LEN.
    • Tiền boa. Để xóa 9 ký tự cuối cùng khỏi ô, hãy thay RIGHT bằng LEFT:
      =LEFT(A1,LEN(A1)-9)
    • Cuối cùng nhưng không kém phần quan trọng là hàm REPLACE. Bạn yêu cầu nó lấy 9 ký tự bắt đầu từ bên trái và thay thế chúng bằng không ( “” ):=REPLACE(A1,1,9,"")
  • Remove the first 9 chars from cells using the REPLACE function.
    • Ghi chú. Vì REPLACE yêu cầu vị trí bắt đầu để xử lý văn bản, nó sẽ không thực hiện được nếu bạn cần xóa N ký tự khỏi phần cuối của ô.

    Cách không cần công thức để xóa văn bản cụ thể trong Google Trang tính – Tiện ích bổ sung Power Tools

    Các chức năng và tất cả đều tốt bất cứ khi nào bạn có thời gian để giết. Nhưng bạn có biết có một công cụ đặc biệt bao gồm tất cả các cách nói trên và tất cả những gì bạn phải làm là chọn nút radio cần thiết? 🙂 Không có công thức, không có cột bổ sung – bạn không thể mong muốn có một sidekick tốt hơn; D

    Bạn không cần phải nghe lời tôi, chỉ cần cài đặt Power Tools và tự mình xem:

    1. Nhóm đầu tiên cho phép bạn xóa nhiều chuỗi con hoặc ký tự riêng lẻ khỏi bất kỳ vị trí nào trong tất cả các ô đã chọn cùng một lúc:
  1. Remove substrings or characters using Power Tools.
  2. Cái tiếp theo loại bỏ không chỉ khoảng trắng mà còn cả ngắt dòng, thực thể & thẻ HTML cũng như các dấu phân cách và ký tự không in khác . Chỉ cần đánh dấu vào tất cả các hộp kiểm cần thiết và nhấn Xóa :
    Remove spaces and delimiters using Power Tools.
  3. Và cuối cùng, có các cài đặt để xóa văn bản trong Google Trang tính theo một vị trí nhất định, N ký tự đầu tiên / cuối cùng hoặc ký tự trước / sau :
    Remove characters by position using Power Tools.

Bạn có thể có tất cả những thứ này và hơn 30 trình tiết kiệm thời gian khác cho bảng tính bằng cách cài đặt tiện ích bổ sung từ Google Store . 30 ngày đầu tiên là hoàn toàn miễn phí và có đầy đủ chức năng, vì vậy bạn có thời gian để quyết định xem nó có đáng để đầu tư hay không.

Ketoanmvb – Dịch vụ kế toán trọn gói giá rẻ nhất dành cho doanh nghiệp