Công thức Excel: Chuẩn hóa văn bản -

Mục lục

Công thức chung

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))

Tóm lược

Để loại bỏ một số độ phức tạp tự nhiên của văn bản (dấu chấm câu, viết hoa thường, loại bỏ khoảng trắng thừa), bạn có thể sử dụng công thức dựa trên hàm SUBSTITUTE, với sự trợ giúp từ các hàm TRIM và LOWER.

Bối cảnh

Có thể đôi khi bạn cần phải loại bỏ một số biến thể của văn bản trước khi xử lý khác. Một ví dụ là khi bạn muốn đếm các từ cụ thể bên trong chuỗi văn bản lớn hơn. Vì Excel không cung cấp hỗ trợ cho các biểu thức chính quy, bạn không thể tạo các kết quả khớp chính xác. Ví dụ, nếu bạn muốn đếm bao nhiêu lần từ "cáo" xuất hiện trong một ô, bạn sẽ phải đếm "cáo". Bạn có thể tìm "fox" (có dấu cách) nhưng điều đó sẽ không thành công với "fox" hoặc "fox". Một cách giải quyết là đơn giản hóa văn bản trước bằng công thức trong cột trợ giúp, sau đó tính số lần chạy trên phiên bản đơn giản hóa. Ví dụ trên trang này cho thấy một cách để làm điều này.

Giải trình

Công thức được hiển thị trong ví dụ này sử dụng một loạt các hàm SUBSTITUTE lồng nhau để loại bỏ dấu ngoặc đơn, dấu gạch ngang, dấu hai chấm, dấu chấm phẩy, dấu chấm than, dấu phẩy và dấu chấm. Quá trình này chạy từ trong ra ngoài, với mỗi SUBSTITUTE thay thế một ký tự bằng một khoảng trắng, sau đó chuyển giao cho SUBSTITUTE tiếp theo. SUBSTITUTE bên trong nhất loại bỏ các dấu ngoặc đơn bên trái và kết quả được chuyển cho SUBSTITUTE tiếp theo, loại bỏ các dấu ngoặc đơn bên phải, v.v.

Trong phiên bản bên dưới, các dấu ngắt dòng đã được thêm vào để dễ đọc và để chỉnh sửa thay thế dễ dàng hơn. Excel không quan tâm đến ngắt dòng trong công thức, vì vậy bạn có thể sử dụng công thức nguyên trạng.

= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))

Sau khi tất cả các thay thế hoàn tất, kết quả được chạy qua TRIM để chuẩn hóa khoảng trắng, sau đó hàm LOWER để buộc tất cả văn bản thành chữ thường.

Lưu ý: Bạn sẽ cần điều chỉnh các thay thế thực tế cho phù hợp với dữ liệu của mình.

Thêm khoảng trắng ở đầu và cuối

Trong một số trường hợp, bạn có thể muốn thêm một ký tự khoảng trắng vào đầu và cuối của văn bản đã làm sạch. Ví dụ, nếu bạn muốn đếm các từ một cách chính xác, bạn có thể muốn tìm từ được bao quanh bởi dấu cách (tức là tìm kiếm "cáo", "bản đồ") để tránh trùng khớp sai. Để thêm khoảng trắng ở đầu và cuối, chỉ cần nối một khoảng trắng ("") vào đầu và cuối: