Hướng dẫn 4 cách tách chữ và số trong Excel cực đơn giản

Nội dung được viết bởi Kim Thu

Khi làm việc với Excel, chúng ta thường gặp các chuỗi ký tự bao gồm cả chữ và số. Và bạn muốn tách riêng hai thành phần này ra nhưng chưa biết làm như nào?

Đừng lo lắng, hôm nay Gitiho sẽ hướng dẫn bạn cách tách chữ và số trong Excel cực kỳ đơn giản. Cùng xem nhé!

Tại sao cần tách chữ và số trong Excel?

Ví dụ, chúng ta có một bảng với cột chứa cả số và dữ liệu như bên dưới:

Ví dụ về tách chữ và số ra khỏi chuỗi trong Excel
Ví dụ về tách chữ và số ra khỏi chuỗi trong Excel

Bây giờ yêu cầu là hãy tách riêng chữ (tương ứng với phòng ban) và số hiệu của mỗi nhân viên trong bảng trên.

Nếu chúng ta nhập thủ công từng ô thì rất mất thời gian. Chính vì thế, đó là lý do chúng ta cần phải biết cách tách chữ và số ra khỏi chuỗi nhanh chóng mà không cần nhập lại dữ liệu. Xem ngay các cách mà chúng tôi hướng dẫn bên dưới nhé!

Hướng dẫn tách chữ và số ra khỏi chuỗi trong Excel

Cách 1: Sử dụng công cụ Flash Fill để tách chữ và số

Tính năng Flash Fill giúp chúng ta điền nhanh các giá trị tự động dựa trên mẫu. Đây là công cụ thông minh giúp bạn tiết kiệm thời gian và công sức khi làm việc trên Excel. Ta dùng Flash Fill để tách chữ và số ra như sau:

Bước 1: Tại cột Phòng ban, nhập ký tự chữ tại ô đầu tiên C2 (VD: HR).

Tương tự ở cột D, hãy gõ số hiệu của nhân viên tương ứng vào ô D2.

Gõ giá trị mẫu vào ô đầu tiên
Gõ giá trị mẫu vào ô đầu tiên

Bước 2: Click lần lượt vào ô C2 D2, nhấn tổ hợp phím tắt Ctrl + E để kích hoạt công cụ Flash Fill. Vậy là bạn đã tách được chữ và số trong chuỗi này nhanh chóng.

Dùng công cụ Flash Fill để tách chữ và số
Dùng công cụ Flash Fill để tách chữ và số

Cách 2: Sử dụng hàm tách số ra khỏi chữ trong Excel và ngược lại

Nếu bạn thành thạo cách dùng các hàm trong Excel thì ngại gì mà không thử phương pháp này. 

Đầu tiên, gõ công thức sau vào ô C2:

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

Trong đó:

  • LEFT(B2, ...) lấy ra một phần của chuỗi ở ô B2 bắt đầu từ ký tự đầu tiên bên trái.
  • FIND({0,1,2,3,4,5,6,7,8,9},B2) sẽ tìm vị trí đầu tiên là số (từ 0 đến 9) xuất hiện trong chuỗi.
  • IFERROR(...,"") sẽ kiểm tra xem có lỗi không. Nếu không tìm thấy số, nó sẽ trả về chuỗi rỗng.
  • MIN(...)-1 lấy ra số ở vị trí đầu tiên trong chuỗi. Do hàm Find sẽ hiển thị tất cả các vị trí nào chứa số, nên ta dùng hàm Min để lấy số ở vị trí đầu tiên trong chuỗi.
Công thức tách chữ ra khỏi chuỗi
Công thức tách chữ ra khỏi chuỗi

Tương tự, chúng ta nhập công thức hàm tách số ra khỏi chuỗi trong Excel vào ô D2:

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

Trong đó: 

  • FIND({0,1,2,3,4,5,6,7,8,9},B2) tìm vị trí đầu tiên mà một số từ 0 đến 9 xuất hiện trong chuỗi của ô B2.
  • IFERROR(...,"") kiểm tra xem nếu có lỗi hoặc chuỗi không có số thì thay thế nó bằng một khoảng trắng ("").
  • MIN(...) tìm số có vị trí nhỏ nhất (số đầu tiên xuất hiện trong chuỗi)
  • MID(B2, ..., 100) lấy một phần của chuỗi bắt đầu từ số đầu tiên và lấy 100 ký tự tiếp theo (tương đương với lấy hết phần còn lại của chuỗi từ vị trí đó).
Công thức tách số ra khỏi chuỗi
Công thức tách số ra khỏi chuỗi

Sau đó kéo và copy công thức từ ô C2 và D2 xuống các ô còn lại, ta thu được kết quả như sau:

Kéo và sao chép công thức trong Excel
Kéo và sao chép công thức trong Excel

Cách 3: Sử dụng VBA để tách số và chữ tự động trong Excel

Nếu bạn đã thành thạo Excel thì có thể cân nhắc học VBA Excel, giúp bạn tối ưu hóa mọi công việc từ đơn giản đến phức tạp trên bảng tính một cách tự động nhanh chóng. Với VBA, bạn cũng có thể tách chữ và số ra khỏi chuỗi tự động chỉ bằng vài thao tác bấm. 

Bước 1: Tại file chứa dữ liệu muốn tách, nhấn Alt + F11 để mở giao diện VBA. Sau đó, nhấn vào menu Insert > chọn tạo một Module mới. Lưu ý bạn cần chọn đúng sheet muốn tách dữ liệu nhé.

Tạo Module mới trong VBA
Tạo Module mới trong VBA

Bước 2: Nhập đoạn code sau vào cửa sổ module:

Sub TachChuVaSo()
    Dim rng As Range
    Dim cell As Range
    Dim i As Integer
    Dim strChu As String
    Dim strSo As String
    
    Set rng = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    
    For Each cell In rng
        strChu = ""
        strSo = ""
        
        For i = 1 To Len(cell.Value)
            If IsNumeric(Mid(cell.Value, i, 1)) Then
                strSo = strSo & Mid(cell.Value, i, 1)
            Else
                strChu = strChu & Mid(cell.Value, i, 1)
            End If
        Next i
        
        cell.Offset(0, 1).Value = strChu
        
        cell.Offset(0, 2).Value = strSo
    Next cell
End Sub

Bước 3: Nhấn vào biểu tượng Run trên thanh công cụ VBA hoặc nhấn F5 để chạy mã. 

Chạy mã VBA
Chạy mã VBA

Tắt trình duyệt VBA, quay trở lại giao diện chính của Excel, chúng ta thu được kết quả như sau:

Chạy mã VBA để tách số và chữ trong sheet
Chạy mã VBA để tách số và chữ trong sheet

Cách 4: Tách chữ và số bằng Power Query

Bước 1:  Để sử dụng Power Query, bạn cần chuyển cột dữ liệu Mã nhân viên của bạn thành Table. Bôi đen vùng dữ liệu đó, chọn thẻ Insert > Table > OK để tạo bảng cho vùng bạn chọn.

Tạo Table cho vùng dữ liệu
Tạo Table cho vùng dữ liệu

Bước 2: Vào menu Data, chọn From Table/ Range. Lúc này, giao diện Power Query sẽ hiện ra.

Mở giao diện Power Query
Mở giao diện Power Query

Bước 3: Vào menu Home > Chọn Split ColumnBy non Digit to Digit

Tính năng By non Digit to Digit
Tính năng By non Digit to Digit

Kết quả là chuỗi của bạn đã được tách riêng chữ và số như sau:

Tách riêng chữ và số bằng Power Query
Tách riêng chữ và số bằng Power Query

Bước 4: Tại menu Home > nhấn vào Close  & Load to… để chuyển dữ liệu từ Power Query về Excel.

Chuyển dữ liệu từ Power Query về Excel
Chuyển dữ liệu từ Power Query về Excel

Bước 5: Trong hộp thoại Import Data, chọn vị trí mà bạn muốn đặt kết quả chữ và số vừa được tách riêng ở mục Existing Worksheet.

Chọn vị trí hiển thị kết quả tại Existing Worksheet
Chọn vị trí hiển thị kết quả tại Existing Worksheet

Nhấn Ok là chúng ta đã có ngay một bảng chứa dữ liệu được tách chữ và số riêng như sau:

Bảng dữ liệu chứ cột số và chữ được tách riêng bằng Power Query
Bảng dữ liệu chứ cột số và chữ được tách riêng bằng Power Query

Lưu ý: Nếu bạn thay đổi dữ liệu ở cột gốc, hãy cập nhật dữ liệu cho 2 cột tách bằng cách nhấn chuột phải vào một ô bất kỳ, chọn Refresh.

Cập nhật dữ liệu khi có thay đổi
Cập nhật dữ liệu khi có thay đổi

Trên đây là 4 cách để tách chữ và số trong Excel, hy vọng nó sẽ hữu ích cho công việc của bạn. Chúc bạn thực hiện thành công!

Deal lớn - Giá hời!

Gitiho giảm giá khóa học Hạch toán Kế toán căn bản (Nguyên lý kế toán), dành cho người mới bắt đầu học về kế toán hoặc không còn nhớ về định khoản kế toán.

Giá gốc của khóa học là 699K, hiện đang được giảm còn 299K! Nhập thêm mã code KETOAN49K để sở hữu khóa học chất lượng chỉ 49K!

Thời hạn áp dụng chỉ đến 01/06 - Nhấn vào banner bên dưới để tìm hiểu thêm!

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

0 thảo luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội
Giấy phép mạng xã hội số: 588, cấp bởi Bộ thông tin và truyền thông