Bạn đang tìm một giải pháp, có thể là một biểu thức chính quy (regex), để loại bỏ các ký tự lặp lại ở cuối chuỗi trong Google Sheets? Thì thì tôi có câu trả lời trong bài viết này!
Cho phép tôi giải thích ý kiến của tôi trước. Khi chúng ta kết hợp hai hoặc nhiều giá trị lại với nhau, để chỉ rõ ranh giới, chúng ta thường sử dụng các ký tự như ~ (dấu ngã), | (dấu gạch đứng), / (dấu gạch chéo), ^ (dấu mũ), v.v. Những ký tự/sign này được gọi là ký tự phân tách.
Khi có những ô trống, sẽ có những ký tự phân tách dư thừa trong chuỗi kết hợp. Xem mẫu dưới đây mà tôi đang nói đến.
Kết hợp các cột B đến E vào F (B2:E6 vào F2):
Tôi muốn loại bỏ những ký tự ngã (dưới gạch dưới) lặp lại đó từ cuối chuỗi, không phải từ bất kỳ phần nào khác.
Chuỗi/phân tách này có thể được bao quanh bởi khoảng trắng hoặc không, phụ thuộc vào phương pháp chúng ta sử dụng để kết hợp các cột.
Trong ví dụ trên, các ký tự phân tách đã được bao quanh bởi khoảng trống (một khoảng trống duy nhất ở cả hai bên).
Mặc dù chúng trông liên tục, có nghĩa là dấu ngã lặp lại, nhưng thực chất không phải vì khoảng trắng.
Đối với quy trình phức tạp hơn, chúng tôi không chắc chắn về số lượng các ký tự lặp lại ở cuối chuỗi vì nó hoàn toàn phụ thuộc vào các ô trống.
Xem xét tất cả những điều trên, làm cách nào để xóa các ký tự lặp lại ở cuối chuỗi trong Google Sheets? Tôi muốn một kết quả sạch như bên dưới.
Sử dụng RegexReplace để xóa các ký tự lặp lại ở cuối chuỗi trong Google Sheets
Kết hợp cột và đặt các ký tự phân tách làm ranh giới
Mặc dù hướng dẫn này không liên quan đến việc kết hợp cột, tôi vẫn cung cấp công thức cho bạn.
Dựa trên ví dụ trên, bạn có thể sử dụng công thức 1 dưới đây trong ô F2 và sao chép-dán nó cho đến F6 hoặc công thức 2 trong ô F2 duy nhất để kết hợp phạm vi B2:E6.
Công thức 1:
=B2&" ~ "&C2&" ~ "&D2&" ~ "&E2
Công thức 2:
=ArrayFormula(B2:B6&" ~ "&C2:C6&" ~ "&D2:D6&" ~ "&E2:E6)
Có cũng một công thức linh hoạt cho việc này. Thay vì kết hợp từng cột riêng lẻ, chúng ta có thể sử dụng mảng/phạm vi B2:E6 để kết hợp các cột.
Hãy xem công thức mảng linh hoạt đó để kết hợp các cột bên dưới, chỉ cần gõ vào ô F2.
Công thức 3:
=ArrayFormula(transpose(QUERY(transpose(B2:E6&" ~ "),,9^9)))
Đến lúc sử dụng công thức có thể loại bỏ các ký tự lặp lại ở cuối chuỗi trong Google Sheets.
Công thức để xóa các ký tự phân tách liên tiếp từ cuối chuỗi trong Google Sheets
Theo như tôi biết, có ba hàm để thay thế phần của một chuỗi trong Google Sheets. Chúng là REPLACE, SUBSTITUTE và REGEXREPLACE. Hàm REGEXEXTRACT cũng hoạt động tốt.
Nhưng hàm tốt nhất để sử dụng để loại bỏ các ký tự không mong muốn lặp lại ở cuối một chuỗi trong Google Sheets không ai khác ngoài REGEXREPLACE. Bạn có biết tại sao?
Trong khi REPLACE yêu cầu vị trí và độ dài của các ký tự cần xóa, SUBSTITUTE không có đối số để chỉ định cuối của chuỗi.
Vì vậy, việc khớp mẫu regex là một giải pháp lý tưởng để loại bỏ các ký tự không mong muốn từ cuối một văn bản trong Google Sheets.
Bạn có thể sử dụng công thức 4 dưới đây trong ô G2 và sao chép-dán nó cho đến ô G6 hoặc sử dụng công thức 5 trong ô G2 duy nhất.
Công thức 4:
=REGEXREPLACE(F2,"(s~s){1,}$","")
Công thức 5:
=ArrayFormula(REGEXREPLACE(F2:F6,"(s~s){1,}$",""))
Đương nhiên, chúng ta có thể thay thế tài liệu tham khảo ô F2 trong công thức 4 bằng công thức 1 cũng như thay thế tham chiếu phạm vi F2:F6 trong công thức 5 bằng công thức 2. Điều này giúp chúng ta có thể làm cột F trống.
Chúng ta không thể thay thế phạm vi F2:F6 bằng công thức 3. Tôi sẽ đến phần kết luận sau đó. Điều đó không quan trọng vào lúc này.
Công thức, đặc biệt là biểu thức chính quy, được giải thích
Trong ví dụ trên, tôi đã sử dụng dấu ~ làm ký hiệu phân tách (giới hạn của mỗi cột).
Trong khi kết hợp các cột, tôi đã đặt khoảng trắng ở hai bên của ký tự phân tách này như ” ~ “. Vui lòng xem công thức 1 hoặc 2.
Sau đây là biểu thức chính quy mà chúng tôi đã sử dụng để loại bỏ các ký tự lặp lại (dấu ngã) từ cuối chuỗi trong các công thức 4 và 5 trong Google Sheets.
(s~s){1,}$
Hãy xem giải thích bên dưới.
- s – khớp với 1 ký tự khoảng trắng.
- ~ – khớp với 1 ký tự dấu ngã.
- {1,} – (quantifier) khớp với 1 hoặc nhiều lần.
- $ – vị trí cuối chuỗi.
Tôi đã sử dụng s cả hai bên dấu ngã để khớp với các ký tự ” ~ “.
Nếu bạn không có khoảng trắng, ví dụ, nhìn vào đoạn văn bản Country 1~Country 6~Country 11~Country 13~~, sử dụng biểu thức chính quy là (~){1,}$.
Công thức REGEXREPLACE thay thế các ký tự khớp mẫu ở trên bằng ký tự “”.
Như ví dụ trên, đây là biểu thức chính quy để loại bỏ các ký tự ^, / và | lặp lại từ cuối chuỗi trong Google Sheets.
(s^s){1,}$
(s/s){1,}$
(s|s){1,}$
Kết luận
Tôi đã để lại một công thức chưa được giải quyết, tức là công thức 3, đã được sử dụng để kết hợp các cột. Đây là nguyên nhân.
Sử dụng REGEXREPLACE không dễ dàng để loại bỏ các ký tự phân tách không mong muốn ở cuối kết quả của công thức đó. Bởi vì công thức đó đặt một số lượng không đều các ký tự khoảng trắng xung quanh ký tự phân tách.
Như một giải pháp, hãy sửa đổi một chút công thức 3 như bên dưới và sau đó sử dụng biểu thức chính quy (~s){1,}$.
Hơn nữa, nếu bạn sử dụng hàm TEXTJOIN để kết hợp giá trị từ nhiều ô hoặc phạm vi, hãy xem bài viết đầu tiên trong phần “Tài nguyên” dưới đây.
Hết thảy là như vậy. Hãy thưởng thức!
Tài nguyên:
- Cách xóa ký tự phân tách dư thừa trong Google Sheets – Kết hợp các cột
- Thay thế nhiều giá trị phân cách bằng dấu phẩy trong Google Sheets
- Biểu thức chính quy để thay thế lần xuất hiện cuối cùng của một ký tự trong Google Sheets
- Trích xuất, thay thế, khớp với lần xuất hiện thứ N của một chuỗi hoặc số trong Google Sheets
- REGEXREPLACE để bao số trong ngoặc đơn và cách sử dụng trong truy vấn
