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.
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:
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:
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.
Ở đâ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:
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:
- 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,"#","")
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 #:
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:
=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’ ( “” ).
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 ô:
REGEXREPLACE sẽ giúp tôi xóa nó như thế nào?
=REGEXREPLACE(A1,"(.*)US(.*)","$1 $2")
Đâ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ỹ .
=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ố:
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 ô?
Đây là công thức tôi sẽ sử dụng:
=REGEXREPLACE(A1,".*n.*(+.*)","$1")
-
- Đâ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:
- Đâ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ó).
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.
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)))
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.
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 ô:
- 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")
.-
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)
-
-
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,"")
-
-
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; DBạ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:
- 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:
-
- 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 :
- 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 :
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
The post 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 appeared first on Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng | KetoanMVB.
source https://ketoanmvb.com/google-trang-tinh-xoa-cung-mot-van-ban-hoac-mot-so-ky-tu.html
Không có nhận xét nào:
Đăng nhận xét