Hàm tách dữ liệu trong Google Sheet

Ứng dụng hàm Regextract là hàm được sử dụng trên Google Sheets, nó sẽ nhận vào tham số đầu tiên là dữ liệu mình muốn xử lý, tham số thứ 2 là một biểu thức chính quy, cụm từ thông dụng – Regular Expression [tên ngắn: Regex].

Regular Expression này còn rất nhiều ứng dụng khác nữa trong việc xử lý dữ liệu, đặc biệt là ứng dụng xử lý chuỗi trích xuất phần phía sau của 1 ký tự như tách địa chỉ website từ email tên miền, tách họ tên, tách email và số điện thoại trong rất nhiều dữ liệu khác nhau….

Trong bài viết này, Xcel Vietnam sẽ hướng dẫn bạn thực hiện các ứng dụng hữu ích này để phục vụ công việc quản lý và xử lý dữ liệu trên Google sheets nhé.

Để tách địa chỉ website từ email tên miền, sẽ có 1 số cách khác nhau. Trong bài viết này, Xcel sẽ hướng dẫn bạn sử dụng hàm Regextract để tách cho nhanh nhé.

Chúng ta có danh sách email như cột B bên dưới và muốn tách riêng lấy tên miền website phía sau “@” của dữ liệu.

Đầu tiên, mình sẽ có 1 cụm từ thông dụng bạn cần nhớ mỗi khi dùng hàm này để tách đằng sau @ đó là: @[.+]. Mình sẽ viết cụm từ này ở ô D1.

Sau đó sẽ sử dụng công thức như sau cho ô D2 bắt đầu tách dữ liệu bên cột B:

=REGEXEXTRACT[B2;$D$1]

Sau đó mình sẽ kéo xuống để sao chép công thức cho các hàng phía dưới. Xcel gợi ý cho bạn đó là dùng công thức mảng Array Fomula để tự động copy công thức xuống dưới mà không phải kéo hay copy công thức cho từng hàng.

Theo đó, công thức đầy đủ cho ô D2 sẽ là: =ArrayFomula[REGEXEXTRACT[B2;$D$1]].

Khi phát hiện ra hàm Regextract thì Xcel thấy đây là 1 kho tàng mà mình có thể khai thác rất nhiều thứ hữu ích từ nó. Chắc hẳn bạn nào đã quen thuộc với hàm tách được họ tên như hàm LEN, hàm FIND, nhưng hôm nay Xcel sẽ chia sẻ cho bạn 1 cách thú vị nữa để tách họ tên trong 1 nốt nhạc trên Google Sheet nhé.

Nhìn vào bảng dữ liệu mẫu bên dưới, mình có dữ liệu họ tên đầy đủ trong cột C, yêu cầu cần giải quyết là tách họ và tên ra thành 2 cột khác nhau.

Mình đã viết sẵn cụm ký tự thông dụng: [^\s]+ và \s[\S+]$ để mình sẽ sử dụng để tách họ tên nhé. Bạn có thể hiểu rằng đây giống như 1 ngôn ngữ lập trình sẵn những ký tự, hẹn 1 ngày gần nhất mình sẽ chia sẻ về những ký tự đặc biệt này có ý nghĩa như thế nào nhé. Trong phần này, chúng mình sẽ tạm công nhận với nhau 2 cụm từ thông dụng này dùng để tách họ và tên nhé.

• Công thức tách riêng họ: =[regexextract[C3:C;$D$1]], để tự động copy công thức xuống những hàng bên dưới sẽ dùng thêm công thức mảng Array Fomula như sau: =arrayformula[regexextract[C3:C;$D$1]]

• Công thức tách riêng tên: =[regexextract[C3:C;$E$1]], để tự động copy công thức xuống những hàng bên dưới sẽ dùng thêm công thức mảng Array Fomula như sau: =arrayformula[regexextract[C3:C;$E$1]]

Để dễ dàng hình dung hơn, hãy xem video bên dưới Xcel quay hướng dẫn trực tiếp cách tách họ và tên trên Google Sheet với hàm RegexExtract nhé:

Có bao giờ bạn có 1 mớ văn bản ở trong đó có kèm cả email và số điện thoại không? Ví dụ như khi chát với khách hàng, bạn sẽ copy toàn bộ cả đoạn nội dung vào cột thông tin. Từ đó sẽ có 1 bài toán là nhặt tách email và số điện thoại của khách hàng sang 2 cột khác nhau, một cách sạch sẽ và tự động.

Đầu tiên, mình sẽ ưu tiên xử lý cột email trước. Cũng giống như ngôn ngữ lập trình sẵn ở phần 2, phần 3 này mình cũng đã có 1 công thức sẵn. Bạn sẽ chỉ cần sao chép công thức này vào bảng dữ liệu của bạn để tách lấy email.

=REGEXEXTRACT[B3;“[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}”] 

Tiếp theo sẽ sao chép công thức cho các hàng phía dưới, hoặc sử dụng hàm Array Fomula. Vậy là chúng ta đã có địa chỉ email được tách từ cột B sang cột D.

Tiếp theo, sẽ là tách số điện thoại. Tư duy sẽ là số điện thoại thường chứa số nên mình sẽ tách số ra. Tuy nhiên, có rất nhiều trường hợp email cũng có chứa số nên mình sẽ thêm 1 cột E để xử lý trung gian với hàm thay thế substitute. Tức là để tách được chính xác số điện thoại, mình cần loại trừ phần email ra khỏi nội dung dữ liệu để đảm bảo rằng cột xử lý trung gian chỉ chứa số ở số điện thoại.

Bây giờ thì chúng ta chỉ cần lọc số điện thoại từ ô xử lý trung gian là xong. Nhưng ở phần tách số điện thoại này, lại nảy sinh ra 1 vấn đề đó là nếu như số điện thoại được viết không liền nhau mà lại viết có dấu cách, ví dụ như: 0987 123 456. Chỉ sử dụng riêng hàm Regexextract thì kết quả sẽ nhận được số đầu tiên đó là 0987 chứ không phải toàn bộ số điện thoại 0987123456.

Vì vậy, bạn cần sử dụng bên trong thêm hàm substitute để nén toàn bộ ký tự trong ô sát vào nhau.

Công thức tách số điện thoại ở ô C3 hoàn chỉnh như sau:

=regexextract[substitute[E3;” “;“”];“\d+”]

Cuối cùng, sao chép công thức cho các hàng phía dưới, hoặc sử dụng hàm Array Fomula là hoàn thành rồi.

Sẽ khá rắc rối nếu như bạn chỉ nghe mình trình bày bằng chữ đúng không? Hãy xem video của Xcel dưới đây cho dễ hiểu hơn nhé. Nếu còn bất cứ thắc mắc nào, đừng ngần ngại để lại bình luận, Xcel sẽ cùng giải đáp nhé.