4 Mẹo Tách Chữ Và Số Ra Từ Chuỗi Trong Excel Có Thể Bạn Chưa ...

Video hướng dẫn

Ví dụ: Mình có bảng dữ liệu như bên dưới:

Yêu cầu: Dựa vào số liệu trong bảng số liệu trên, bạn sẽ tách những chuỗi trong cột Emloyee Id ra thành 2 phần: tách ra mã bộ phận của nhân viên vào cột Emp Department và tách ra số nhân viên ra vào trong cột Emloyee No.

Để làm được việc này, mình sẽ qua 4 mẹo tách dữ liệu như sau:

Mẹo 1: Tách chữ và số ra bằng cách sử dụng Flash Fill

Kể từ phiên bản 2013 trở đi, Excel có thêm một công cụ mới hết sức tiện ích, đó là Flash Fill. Đây là một tính năng giúp người dùng điền các giá trị một cách tự động, giúp tiết kiệm thời gian và hạn chế phải sử dụng các hàm không cần thiết.

Để sử dụng công cụ này, bạn sẽ thực hiện các bước sau:

Bước 1: Trong 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.

Tương tự, ở cột Emloyee No, bạn cũng 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 để hiển thị kết quả phía sau.

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.

Mẹo 2: Tách chữ và số ra bằng cách sử dụng công thức

Để sử dụng cách này, tại ô đầu tiên trong cột Emp Department, bạn gõ công thức sau: =LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1)

Giải thích: Find: Dùng hàm này để tìm ra vị trí của các con số lần lượt từ 0 -> 9. A2: Ô chứa ID đầu tiên muốn tách mã bộ phận nhân viên. IFERROR: Sử dụng hàm này để phòng trong trường hợp nếu tìm không thấy những con số hoặc bị lỗi thì sẽ hiển thị ra dấu khoảng trắng "". Min: Vì trong trường hợp dùng hàm FIND thì những vị trí nào nào chứa những con số thì đều hiển thị ra. Tuy nhiên, mình chỉ muốn lấy vị trí đầu tiên xuất hiện những con số nên dùng hàm MIN để lấy ra số vị trí nhỏ nhất. LEFT: Dùng hàm này để tách ra mã bộ phận nhân viên từ ô ID. Cuối cùng trừ 1 để chỉ tính số lượng mã bộ phân nhân viên từ ô ID. Số lượng đó phải trước những con số như trong dữ liệu.

Tương tự, mình cũng tách ra số lượng nhân viên vào cột Emloyee No, bạn cũng gõ: =MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")),100)

Giải thích: Find: Dùng hàm này để tìm ra vị trí của các con số lần lượt từ 0 -> 9. A2: Ô chứa ID đầu tiên muốn tách số lượng nhân viên. IFERROR: Sử dụng hàm này để phòng trong trường hợp nếu tìm không thấy những con số hoặc bị lỗi thì sẽ hiển thị ra dấu khoảng trắng "". Min: Vì trong trường hợp dùng hàm FIND thì những vị trí nào nào chứa những con số thì đều hiển thị ra. Tuy nhiên, mình chỉ muốn lấy vị trí đầu tiên xuất hiện những con số nên dùng hàm MIN để lấy ra số vị trí nhỏ nhất. MID: Dùng hàm này để tách ra số lượng nhân viên từ ô ID. 100: Dùng giá trị 100 để ước lượng tương đối số kí tự sau đó.

Mẹo 3: Tách chữ và số ra bằng cách sử dụng VBA

Để sử dụng phương pháp này, đầu tiên bạn phải truy cập vào giao diệnVBA bằng cách:

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

Hiện ra giao diện VBA. Tiếp theo, bạn sẽ tạo module để chứa code tách dữ liệu bằng cách vào thẻ Insert => chọn vào Module.

Hiển thị giao diện module, tại đây bạn gõ đ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

Tiếp theo, bạn nhấn phím Run trên thanh công cụ hoặc nhấn phím F5 để chạy đoạn code trên.

Sau khi chạy xong thì thoát giao diện VBA ra.

Ở cột Emp Department, ô đầu tiên bạn gõ: =GetText(A2)

Giải thích: Hàm GetText có được do mình tạo hàm từ Code VBA.

Tương tự, để hiển thị cột Emloyee No, tại ô đầu tiên bạn gõ: =GetNumber(A2)

Giải thích: Hàm GetNumber có được do mình tạo hàm từ Code VBA.

Mẹo 4: Tách chữ và số ra bằng cách sử dụng Power Query

Trước khi sử dụng mẹo này, thì bạn sẽ phải chuyển dữ liệu của bạn sang thành bảng Table bằng cách bôi đen vùng dữ liệu muốn tách, sau đó chọn vào thẻ Insert => Table và nhấn Ok để hoàn thành việc tạo bảng.

Sau khi một bảng được tạo, bây giờ tôi có thể sử dụng truy vấn nguồn để đưa tất cả dữ liệu này vào trình chỉnh sửa truy vấn nguồn. Sau đó mình sẽ tách văn bản và số trong trình chỉnh sửa truy vấn nguồn. Vì vậy, để làm điều đó, mình sẽ đi đến thẻ Data, tại đây bạn chọn vào From Table/Range.

Hiển thị ra giao diện Power Query, tại đây bạn sẽ bôi đen vùng dữ liệu cần tách bằng cách chọn vào tiêu đề.

Tiếp theo, vào thẻ Home => Split Column => By non Digit to Digit

Sau khi nhấn chọn xong, dữ liệu của bạn đã được tách

Hoàn thành xong, thì bạn sẽ tải dữ liệu về File Excel bằng cách vào thẻ Home => Close & Load to...

Trong bảng Import Data, bạn chọn vào Existing worksheet và chọn vào vị trí mà bạn muốn hiển thị kết quả.

Nhấn OK để hoàn thành.

Lưu ý: Ở mẹo này thú vị ở chỗ khi bạn sửa thông tin trong cột dữ liệu gốc thì bảng dữ liệu mới tách sẽ tự cập nhật bằng cách chọn ô bất kì trong vùng dữ liệu mới tắt, nhấn chuột phải và chọn vào Refresh.

Vậy là mình đã hướng dẫn xong cho bạn 4 mẹo tách chữ và số ra từ chuỗi trong excel có thể bạn chưa biết. Hy vọng bài viết này sẽ giúp ích cho bạn. Nếu thấy hay đừng quên đánh giá bài viết bên dưới nhé! Trân trọng.