Nếu gặp một bảng số liệu mà các giá trị chữ và số chưa được phân tách, bạn sẽ thao tác tách chữ như thế nào. Cách tách chữ trong excel với các hàm số thông dụng trong bài viết dưới đây sẽ giúp bạn xử lý nhanh chóng vấn đề này.
Nội dung bài viết
Tách chữ trong excel với hàm LEFT
Một trong các hàm xử lý chuỗi trong excel thường được dùng để phân tách ký tự chứa chữ cái ra khỏi một ô excel chứa cả ký tự chữ và số đó là hàm LEFT.
Công thức của hàm LEFT được viết như sau: =LEFT(text; n)
Trong công thức này, chúng ta sẽ định nghĩa text là chuỗi ký tự chứa cả số và chữ mà bạn muốn tách chữ ra khỏi nó; n là số lượng ký tự cần tách ra khỏi chuỗi tính từ chữ cái đầu tiên theo thứ tự từ trái qua phải.
Giả sử bạn có một bảng mã nhân viên, bạn cần tách các ký tự chữ ra khỏi mã nhân viên, chúng ta có cách tách chữ trong excel đơn giản đó là đánh cú pháp tách chữ với hàm LEFT như sau: =LEFT(A3;4)
Trong công thức áp dụng vào ví dụ, A3 là cột chứa mã nhân viên, 4 là số lượng ký tự chữ cần tách ra khỏi mã.
Trong trường hợp giá trị cần tách chữ ra khỏi số có chứa dấu gạch nối, bạn sẽ thao tác như thế nào. Ví dụ được đưa ra là một danh sách các SKU tập hợp sản phẩm – màu sắc – size đi kèm. Bạn cần chia SKU thành 3 cột item, color, size riêng biệt. Chúng ta sẽ có cách tách chữ trong excel với hàm LEFT để trích xuất mục item bao gồm các ký tự trước dấu nối đầu tiên bằng cách nhập cú pháp sau:
= LEFT (A2, search (“-“, A2,1) -1).
Ở công thức này, search sẽ định vị vị trí của dấu noois dầu tiên (-) trong chuỗi ký tự. Áp dụng hàm LEFT sẽ phân tách tất cả các ký tự còn lại. Con số -1 tức là trừ 1 từ vị trí của dấu nối, bỏ qua dấu nối.
Để tách các cột còn lại với một bảng giá trị cần tách nhiều bộ phận chữ, bạn sẽ phải dùng nhiều hàm số khác nhau tùy theo vị trí chữ cần phân tách. Lúc này, chúng ta sẽ có cách tách chữ trong excel với hàm MID.
Tách chữ với hàm MID trong excel
Hàm MID trong excel được tóm tắt bằng công thức sau:
MID (văn bản, start_num, num_chars)
Trong công thức này, “văn bản” được hiểu là nơi để trích xuất văn bản chứa từ, Start_num quy định vị trí của kí tự đầu tiên để phân tách; Num_chars – số lượng ký tự cần phân tách.
Vẫn ở ví dụ trên, sau khi đã tách các ký tự trước dấu gạch nối đầu tiên thành cột item. Bạn sẽ tiếp tục tách chữ giữa dấu gạch nối thứ 1 và thứ 2 để được cột màu sắc (color). Chúng ta có công thức hàm mid như sau:
=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1
Trong công thức áp dụng với ví dụ, A2 là văn bản được trích ra từ ô A2, chúng ta sử dụng 4 hàm search khác nhau để tính 2 đối số.
SEARCH (“-“, A2) + 1: Số bắt đầu (start_num) là vị trí của dấu nối đầu tiên +1
SEARCH (“-“, A2, SEARCH (“-“, A2) +1) – SEARCH (“-“, A2) -1: Số ký tự để trích xuất (num_chars) được hiểu trong thành phần này chính là sự khác biệt giữa vị trí của dấu nối thứ hai và dấu nối đầu tiên, trừ đi 1.
Ví dụ khác về cách tách chữ trong excel với hàm MID. Bạn có bảng mã nhân viên, bạn muốn tách 2 ký tự trong mã bắt đầu từ ký tự thứ 3. Bạn sẽ sử dụng hàm MID như sau: =MID(A3;3;2)
Ở đây, A3: là ô excel chứa chuỗi ký tự mã nhân viên, 3 là vị trí bắt đầu tách chữ, 2 là số lượng ký tự chiết tách.
Tách chữ với hàm RIGHT trong excel
Để giúp bạn hiểu rõ hơn về cách tách chữ trong excel với hàm RIGHT, bài viết sẽ minh họa ví dụ về tách chuỗi ký tự sau dấu @ với địa chỉ mail. Bạn sẽ áp dụng công thức của hàm RIGHT để tách chuỗi ký tự như sau:
=RIGHT(A3;LEN(A3)-SEARCH(“@”;A3))
Trong đó:
A3 là ô excel chứa dữ liệu văn bản cần tách
LEN(A3)-SEARCH(“@”;A3): Do văn bản chữ cần tách nằm ở bên phải nên hàm LEN(A3) là số ký tự của chuỗi A3. SEARCH(“@”;A3) để xác định vị trí ký tự sau @ nằm trong chuỗi cần tách chữ.
Hàm tách chuỗi của loại văn bản kèm số
Rất nhiều tình huống bạn gặp phải các bảng số liệu có một cột gia trị gồm các chuỗi với ký tự chữ và số kết hợp. Bạn muốn phá vỡ quy luật của chúng bằng cách phân tách chữ và số ra hai cột riêng biệt. Lúc này bạn phải vận dụng các hàm xử lý chuỗi một cách hiệu quả.
Chúng ta sẽ đi vào phân tích ví dụ cụ thể để biết cách tách chữ trong excel riêng biệt với số. Bạn có một cột string gồm chuỗi ký tự có tên loại hàng và số hiệu kèm theo. Bạn cần tách chữ vào cột text, số vào cột number.
Bước 1: chúng ta trích xuất cột Number bằng công thức mảng như sau:
= RIGHT (A2, SUM (LEN (A2) – LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7” , “8”, “9”}, “”))))\
Về cơ bản, công thức với hàm số RIGHT ở đây sẽ tìm kiếm mọi số có thể từ 0 đến 9 trong chuỗi nguồn, tính số lượng và trả về ký tự nằm bên phải của chuỗi từ chuỗi ban đầu.
Bước 2: Trích xuất cột text bằng công thức sau:
LEFT (A2, LEN (A2) -LEN (C2))
Bài viết trên đây đã giới thiệu cho bạn các cách tách chữ trong excel với các hàm số RIGHT, LEFT, MID. Tùy theo số lượng chữ trong chuỗi giá trị và yêu cầu tách chữ với số riêng biệt bạn sẽ có cách vận dụng sao cho linh hoạt, phù hợp. Hãy thử áp dụng để làm việc nhanh chóng, hiệu quả hơn với các hàm tách chữ trong excel.
