Hướng dẫn cách tách số và chữ ra khỏi chuỗi trong Excel từ cơ bản đến nâng cao

Lợi ích của việc tách chữ và số ra khỏi chuỗi trong Excel

Nắm được cách tách số và chữ ra khỏi chuỗi trong Excel có thể hỗ trợ bạn tiết kệm thời gian, công sức trong nhiều đầu việc. Một số lợi ích nó mang lại có thể kể đến như là:

• Giúp chúng ta chia nhỏ được dữ liệu ra các ô riêng biệt, từ đó nguồn thông tin hiển thị sẽ trở nên rõ ràng hơn, người đọc cũng dễ hình dung hơn.

• Giúp tách được cả các ký tự đặc biệt trong bảng thông tin dữ liệu. Điều này giúp chúng ta có thể tìm kiếm nhanh chóng các giá trị theo định dạng cụ thể khi cần thiết.

• Hay trong một vài trường hợp chúng ta chỉ cần giữ lại những chuỗi ký tự số hoặc chữ, việc tách ra giúp dữ liệu sạch sẽ hơn và dễ dàng phân tích hơn (như là mã số sinh viên, mã số nhân viên,...) 

Cách tách số và chữ trong Excel từ một chuỗi đơn giản

Dưới đây là 4 cách tách chữ và số từ một chuỗi dữ liệu cơ bản nhất. Mời bạn theo dõi để hiểu rõ hơn cách sử dụng nhé!

Sử dụng công cụ Flash Fill siêu dễ

Flash Fill là một trong những công cụ hữu ích giúp người dùng Excel thực hiện các thao tác lặp lại một cách nhanh chóng. Việc tách số ra khỏi chữ hay tách chữ ra khỏi số trong Excel cũng có thể sử dụng Flash Fill hiệu quả. Tuy nhiên công cụ này chỉ có từ phiên bản 2013 trở đi thôi nhé!

Các bước sử dụng Flash Fill để tách chữ và số như sau: 

Bước 1: Tại cột Emp Department, tại ô đầu tiên, bạn gõ mã bộ phận của nhân viên đầu tiên vào như hình dưới đây

Ở cột Emloyee No, bạn tiếp tục gõ số lượng nhân viên dựa vào ô đầu tiên trong cột Emloyee ID.

Bước 2: Lần lượt tại ô hiển thị kết quả đầu tiên ở cột Emp Department, bạn nhấn phím Ctrl + E. Tương tự, tại ô đầu tiên trong cột Emloyee No, bạn nhấn phím Ctrl + E để hiển thị kết quả phía sau.

Với những phiên bản Excel cũ hơn 2013 các bạn có thể tìm hiểu về các hàm tách số ra khỏi chữ trong Excel dưới đây.

Sử dụng các hàm LEFT, RIGHT, MID

Các hàm LEFT, RIGHT, MID trong Excel cũng được sử dụng để tách số và chữ ra khỏi chuỗi trong Excel. Cụ thể là:

Đối với hàm LEFT

Chúng ta thường sử dụng hàm LEFT để thực hiện tách chữ trong ô Excel từ bên trái sang. Hàm LEFT có công thức dạng: =LEFT(text; [num_chars])

Trong đó:

• Text là chuỗi ký tự cần tách ra, có thể sử dụng một ô Excel bất kỳ hợp lệ để làm tham chiếu cho chuỗi này.

• Num_chars là số nguyên không âm và nó quy định số lượng ký tự bạn muốn lấy tính từ phía bên trái của chuỗi ký tự. 

Ví dụ: Tách 2 ký tự đầu tiên trong mã nhân viên của bảng dưới đây, bạn sử dụng hàm LEFT như sau:

• Bước 1: Hãy lựa chọn ô Excel mà bạn muốn kết quả hiển thị

• Bước 2: Bạn cần nhập công thức =LEFT(A2;2) vào ô vừa chọn. (A2 là ô chưa mã số nhân viên, số 2 là số lượng ký tự muốn tách từ bên trái của chuỗi).

• Bước 3: Chúng ta thu được kết quả là phần chữ đầu tiên của mã nhân viên như hình dưới 

Lưu ý: Nếu bạn không chỉ định giá trị cho num_chars thì Excel sẽ tự hiểu rằng bạn muốn lấy ra ký tự đầu tiên của chuỗi đó. 

Đối với hàm RIGHT

Ngược lại với hàm LEFT, chúng ta sử dụng hàm RIGHT là cách tách số và chữ ra khỏi chuỗi trong Excel từ bên phải sang. Hàm có công thức dạng: =RIGHT(text; [num_chars])

Trong đó:

• Text là chuỗi ký tự cần tách, bạn có thể lấy một ô Excel hợp lệ bất kỳ để làm tham chiếu cho chuỗi này.

• Num_chars là số nguyên không âm với mục đích quy định số lượng ký tự cần lấy tính từ phải sang của chuỗi.

Ví dụ: Tách 3 ký tự số cuối cùng trong mã nhân viên theo danh sách dưới đây, chúng ta thực hiện như sau: 

• Bước 1: Bạn cần chọn ô Excel muốn hiển thị kết quả

• Bước 2: Hãy nhập công thức =RIGHT(A2;3) vào ô được chọn ở trên. (Với A2 là ô chứa mã nhân viên và 3 là số lượng ký tự muốn tách tính từ bên phải sang).

• Bước 3: Với cách tách số trong 1 ô Excel này chúng ta thu được kết quả là phần số cuối cùng của mã số nhân viên. 

Đối với hàm MID

Trong công thức tách số và chữ trong Excel chúng ta cũng có thể sử dụng MID. Hàm MID dùng để tách nội dung trong Excel từ một vị trí bất kỳ, hàm có công thức như sau: =MID(text; start_num; num_chars)

Trong đó:

• Text là chuỗi ký tự muốn tách và bạn có thể sử dụng một ô Excel hợp lệ để làm tham chiếu cho chuỗi.

• Start_num là số nguyên dương, biểu thị vị trí bắt đầu tách ký tự trong chuỗi

• Num_chars là số nguyên không âm và quy định số lượng ký tự bạn muốn tách tính từ vị trí Start_num.

Ví dụ: Tách 2 ký tự trong mã số nhân viên bắt đầu từ vị trí thứ 3, chúng ta thực hiện như sau:

• Bước 1: Chọn ô Excel bạn muốn kết quả được hiển thị.

• Bước 2: Nhập vào ô được chọn công thức =MID(A2; 3; 2). (Trong đó A2 là ô chứa mã số nhân viên, 3 là vị trí bắt đầu tách ký tự, 2 là số lượng ký tự muốn tách tính từ vị trí thứ 3).

• Bước 3: Sau khi hoàn tất chúng ta thu được kết quả 2 ký tự được tách từ mã số nhân viên.

Như vậy, so với hàm LEFT và RIGHT thì hàm MID linh hoạt hơn trong việc tách ký tự từ chuỗi. Một lưu ý khác bạn cần nắm đó là, Start_num phải lớn hơn 0. Trường hợp nếu Start_num bằng 1 thì kết quả của hàm MID và LEFT sẽ tương tự nhau.

Kết hợp các hàm trên với hàm Search

Ngoài cách tách số và chữ ra khỏi chuỗi trong Excel kể trên, bạn cũng có thể kết hợp hàm LEFT, RIGHT hoặc MID với hàm SEARCH để thực hiện tách ký tự trước hoặc sau một ký tự đặc biệt. Hay nói cách khác, hàm SEARCH được sử dụng để tìm vị trí của một ký tự cụ thể nằm trong chuỗi ký tự. Hàm có công thức như sau: =SEARCH(find_text; within_text; [start_num])

Trong đó:

• Find_text là chuỗi ký tự cụ thể mà bạn cần tìm vị trí.

• Within_text là chuỗi ký tự bạn muốn tìm trong đó.

• Start_num là vị trí ký tự mà bạn muốn bắt đầu tìm kiếm trong Within_text

Ví dụ: Tách các ký tự trước số 0 trong cột thông tin ở bảng Excel dưới đây, chúng ta thực hiện như sau:

• Bước 1: Bạn cần chọn ô Excel hiển thị kết quả.

• Bước 2: Nhập vào ô đã chọn công thức =LEFT(A2,SEARCH(“0”,A2)-1). Trong đó, A2 là ô chứa dữ liệu cần tách, bạn trừ đi 1 thì sẽ thu được vị trí ký tự cuối cùng trước số 0, sau đó hàm LEFT sẽ tách các ký tự đó ra.

• Bước 3: Chúng ta sẽ thu được kết quả là phần ký tự trước sô 0 trong cột thong tin. 

Lưu ý: trong công thức hàm SEARCH thì tham số Find_text và Within_text là bắt buộc còn Start_num là tùy chọn.

Cách tách chữ và số thành nhiều cột từ một chuỗi phức tạp

Đối với các chuỗi ký tự phức tạp thì những cách bên trên sẽ không còn hiệu quả nữa. Lúc này các bạn cần sử dụng các hàm hoặc công cụ nâng cao hơn. Dưới đây sẽ là 3 cách thông dụng được sử dụng nhiều nhất mà bạn có thể tham khảo: 

Sử dụng công cụ Flash Fill (trong số ít trường hợp)

Công cụ Flash Fill là một trong những cách tách chữ và số thành 2 cột trong Excel hiệu quả, vậy nên trong một vài trường hợp phức tạp chúng ta vẫn có thể áp dụng công cụ trên.

Ví dụ cụ thể: Chúng ta có bảng dữ liệu gồm chữ và số xen kẽ nhau như hình dưới đây.

Trường hợp này nếu sử dụng hàm LEFT, RIGHT hay MID thì đều sẽ rất tốn công sức và thời gian. Do đó, Flash Fill sẽ là công cụ hữu ích nhất lúc này. Chúng ta sẽ thực hiện các bước như sau:

Bước 1: Bạn cần nhập số đầu tiên cần tách ở dữ liệu đầu tiên ra ô Mã số.

Bước 2: Click chọn ô Mã số vừa chọn ở trên -> nhân chọn thẻ Data -> Đến mục Data Tools -> Click chọn Flash Fill để dữ liệu điền tự động.

Bước 3: Chúng ta sẽ nhận được kết quả các số trong dãy dữ liệu được tách ra nhanh chóng dù không giống nhau về độ dài lẫn con số.

Sử dụng VBA

Để áp dcách tách số và chữ ra khỏi chuỗi trong Excel này thì bạn cần truy cập vào giao diện VBA, cụ thể là:

• Tại trang tính Excel chứa dữ liệu cần tách, bạn nhấn tổ hợp ALT + F11.

• Giao diện VBA hiện ra, bạn tạo module để chứa code tách dữ liệu bằng cách vào thẻ Insert => chọn Module.

• Sau khi giao diện module hiển thị, bạn gõ vào đoạn code sau: 

“ Function GetNumber(CellRef As String)

Dim StringLength As Integer

StringLength = Len(CellRef)

For i = 1 To StringLength

If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)

Next i

GetNumber = Result

End Function

Function GetText(CellRef As String)

Dim StringLength As Integer

StringLength = Len(CellRef)

For i = 1 To StringLength

If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)

Next i

GetText = Result

End Function ”

• Sau đó bạn nhấn phím Run trên thanh công cụ hoặc F5 để thực hiện chạy đoạn code trên.

• Sau khi chạy xong hãy thoát giao diện VBA. Ở cột Emp Department, ô đầu tiên bạn gõ: =GetText(A2) (Hàm GetText được tạo ra từ Code VBA)

• Tiếp theo để hiện thị cột Emloyee No, bạn gõ: =GetNumber(A2) tại ô đầu tiên (Hàm GetNumber cũng được tạo từ Code VBA)

Sử dụng các hàm nâng cao

Ngoài các cách trên bạn cũng có thể sử dụng các hàm nâng cao. Hàm nâng cao này là sự kết hợp của nhiều hàm với nhau bao gồm hàm LEFT, hàm MIN, hàm IFERROR và hàm FIND. Công thức của hàm tách chữ ra khỏi số trong Excel này như sau: 

=LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1)

Trong đó:

• Find được sử dụng để tìm ra vị trí của các con số từ 0-9

• A2 là ô chứa ID đầu tiên muốn tách trong mã bộ phận nhân viên.

• IFERROR được sử dụng để phòng trường hợp tìm không thấy các con số hoặc bị lỗi sẽ hiển thị khoảng trắng.

• Min dùng để lấy ra số vị trí nhỏ nhất.

• LEFT dùng để tách mã bộ phận nhân viên từ ô ID.

• Trừ 1 để chỉ tính số lượng mã bộ phận nhân viên từ ô ID.

Tương tự như trên, chúng ta có hàm tách số ra khỏi chữ trong Excel với công thức như sau:

=MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")),100)

Trong đó:

• Find được sử dụng để tìm ra vị trí của các con số từ 0-9

• A2 là ô chứa ID đầu tiên muốn tách số lượng nhân viên.

• IFERROR được sử dụng để phòng trường hợp tìm không thấy các con số hoặc bị lỗi sẽ hiển thị khoảng trắng.

• Min dùng để lấy ra số vị trí nhỏ nhất.

• MID dùng để tách ra số lượng nhân viên từ ô ID

• 100 dùng để ước lượng tương đối số ký tự sau đó.

Kết luận

Như vậy, chúng ta đã tìm hiểu chi tiết về cách tách số và chữ ra khỏi chuỗi trong Excel. Hy vọng những kiến thức mà Thủ Thuật Số chia sẻ sẽ phần nào hữu ích và đem lại hiệu quả cho công việc của bạn. Tìm hiểu thêm các thủ thuật Excel thú vị khác tại trang web của chúng tôi nhé!