Tách số khỏi văn bản trong công thức excel

Chúng ta thường phải tách văn bản và số khỏi một số dữ liệu trong Excel. Hôm nay tôi sẽ chỉ cho bạn 4 cách dễ dàng để bạn có thể thực hiện điều đó một cách thuận tiện.

Mục lục

Tải sách bài tập thực hành

Văn bản và số riêng biệt. xlsm

6 cách thích hợp để tách văn bản và số trong Excel

Chúng ta hãy xem bộ dữ liệu này. Chúng tôi có Dữ liệu tổng hợp của một số học sinh của một trường tên là Trường Saint Xaviers

Chúng tôi có hai cột riêng biệt. Cột C và D, nơi chúng tôi muốn trích xuất Tên sinh viên và ID sinh viên riêng biệt. Làm thế nào bạn có thể làm điều đó?

1. Sử dụng menu Chuyển văn bản thành cột để phân tách văn bản và số trong Excel

Đây là phương pháp đầu tiên tôi sẽ mô tả ở đây. Tôi sẽ sử dụng menu văn bản thành cột để tách văn bản và số trong excel. Hãy làm theo các bước của phương pháp

bước.  

• Chọn các ô mà bạn muốn tách văn bản và số. Ở đây tôi chọn các ô từ B4 đến B13

• Sau đó vào menu Data>Text to Columns trong Excel Toolbar dưới phần Data Tools

• Sau đó, nhấp vào nó. Sau đó, bạn sẽ nhận được Hộp Thuật sĩ Chuyển đổi Văn bản thành Cột. Vì chúng tôi muốn phân tách văn bản và số bằng Dấu phân cách (dấu phẩy), hãy kiểm tra tùy chọn Phân cách
• Do đó, bạn có thể xem bản xem trước dữ liệu của mình
• Sau đó nhấp vào Tiếp theo

• Sau đó, bạn sẽ chuyển sang bước 2 của Trình hướng dẫn chuyển đổi văn bản thành cột
• Nếu bạn muốn phân tách văn bản và số bằng dấu phẩy (,), hãy chọn dấu phẩy (,) từ tùy chọn Dấu phân cách. Bạn có thể chọn bất cứ điều gì nhiều hơn nếu bạn cần
• Và tất nhiên, bạn có thể chọn nhiều Dấu phân cách cùng nhau
• Bây giờ bạn sẽ thấy bản xem trước dữ liệu của mình được chia
• Sau đó nhấp vào Tiếp theo

• Bây giờ bạn sẽ được chuyển hướng đến bước 3 của Hộp Thuật sĩ Chuyển đổi Văn bản thành Cột. Đây là bước cuối cùng
• Ở dưới cùng của hộp, bạn sẽ thấy dữ liệu của mình được chia thành số lượng cột cần thiết. Trong trường hợp này, trong hai cột
• Chọn từng cột và sau đó trong tùy chọn Định dạng dữ liệu cột, hãy chọn định dạng mà bạn muốn có cột đó
• Trong trường hợp này, tôi muốn cả hai cột ở định dạng Chung
• Và sau đó trong hộp Đích, hãy viết Tham chiếu ô tuyệt đối của ô ngoài cùng bên trái của phạm vi mà bạn muốn dữ liệu của mình được chia
• Hoặc nhấp vào ô nhỏ bên phải và chọn thủ công ô ngoài cùng bên trái của Destination range
• Ở đây mình chọn là $C$4
• Sau đó bấm Kết thúc

• Bạn sẽ thấy Tên học sinh và Mã số học sinh được chia đẹp mắt thành hai cột như thế này

Đọc thêm. Cách tách các số trong một ô trong Excel (5 phương pháp)

2. Tách Văn bản và Số trong Excel Chèn Flash Fill từ Thanh công cụ Excel vào

Ở đây, giả sử trong tập dữ liệu, bạn không có dấu phẩy (,) trong Dữ liệu kết hợp. Như thế này. làm theo phương pháp từng bước được đề cập dưới đây

Bây giờ quá trình nêu trên sẽ không hoạt động. Vì vậy, làm thế nào bạn có thể tách các văn bản và số lần này? . Cách tách tốt nhất hiện nay là dùng Flash Fill. Bạn có thể sử dụng nó theo hai cách

bước

• Đầu tiên, tách dữ liệu đầu tiên theo cách thủ công. Ở đây tôi đặt Steve Morris ở ô C4 và 101 ở ô D4

• Sau đó chọn các ô còn lại trong cột đầu tiên. Tôi chọn tất cả các ô từ ô C4 đến C13

• Sau đó vào Data>Flash Fill trong Excel Toolbar dưới phần Data Tools

• Nhấp vào Flash Điền. Bạn sẽ tìm thấy văn bản từ tất cả các ô của cột B được sắp xếp đẹp mắt trong cột C

• Làm điều đó cho các cột còn lại. Tôi làm lại cho cột D

3. Giới thiệu Flash Fill From Fill Handle để tách văn bản và số trong Excel

Ở đây mình sẽ sử dụng Flash Fill từ Fill handle để tách chữ và số trong excel. Bạn có thể sử dụng Fill Handle của Excel để sử dụng Flash Fill. Thực hiện theo các bước dưới đây

bước

• Tách ô đầu tiên theo cách thủ công. Tôi đặt Steve Morris vào ô C4 và 101 vào ô D4
• Sau đó kéo Fill Handle của cột đầu tiên qua các ô còn lại. Bạn sẽ nhận được một biểu tượng nhỏ có tên Tùy chọn điền tự động ở góc dưới cùng bên phải sau khi kéo

• Nhấp vào menu thả xuống được liên kết với nó. Bạn sẽ nhận được bốn tùy chọn. Nhấp vào Flash Điền

• Bạn sẽ tìm thấy văn bản từ tất cả các ô của cột B được sắp xếp đẹp mắt trong cột C

• Sau đó thực hiện cho các ô còn lại. Tôi làm lại cho cột D

Ghi chú. Điền Flash có sẵn từ Excel 2013. Người dùng các phiên bản trước sẽ không nhận được nó.

Đọc thêm. Chỉ trích xuất các số từ ô Excel (6 phương pháp hữu ích)

4. Tách văn bản và số trong Excel Chèn hàm MIN và hàm FIND

Nó sẽ hữu ích khi chúng ta có dữ liệu kết hợp không được phân tách bằng dấu phẩy (,). Giống như cái, chúng tôi đã sử dụng trong phần 2. Hàm MIN lấy một mảng số làm đầu vào. Sau đó, trả về số lượng tối thiểu như đầu ra. Ngoài ra, hàm FIND có ba đầu vào, find_text (có thể là một giá trị đơn lẻ hoặc một mảng), within_text, start_num. Sau đó, Trả về vị trí mà đầu vào find_text khớp với bên trong văn bản within_text. Sau đó, bắt đầu tìm kiếm từ vị trí start_num

bước

• Đầu tiên, viết công thức sau vào ô E5

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))

Đây,

• Hàm TÌM nhận đầu vào {0,1,2,3,4,5,6,7,8,9}.
• Hàm này tìm giá trị trong ô B5 có số bắt đầu bằng 0123456789

Lần nữa,

• Hàm trả về giá trị nhỏ nhất nhận được từ hàm find

• Do đó, trong cái tên Steve Morris101, các con số bắt đầu từ vị trí thứ 13
• Ở đây, công thức tách Name sẽ là

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

Đây,

• Hàm LEFT lấy đối số từ ô B5, hàm MIN và FIND trả về giá trị bên trái của ô B5

• Hơn nữa, nó tách tên Steve Morris thành công

• Sau đó, kéo Fill Handle để tách tên cho các ô còn lại

• Và đối với Student Is, công thức sẽ là

=RIGHT(B4,LEN(B4)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))+1)

Đây,

• Hàm RIGHT lấy các đối số từ ô B5, hàm MIN và hàm FIND trả về giá trị ngoài cùng bên phải của B5

• Cuối cùng, nhập công thức này vào ô đầu tiên rồi kéo Fill Handle. Bạn cũng sẽ nhận được ID sinh viên

Ghi chú.  Nếu bạn có số trước và sau đó là văn bản trong dữ liệu kết hợp, chẳng hạn như 101Steve Morris, thì công thức cho số sẽ được hoán đổi với công thức cho văn bản. Và bạn phải sử dụng hàm MAX thay cho hàm MIN.

5. Sử dụng hàm RIGHT, LEFT và SEARCH để phân tách văn bản và số trong Excel

Nếu dữ liệu kết hợp được phân tách bằng dấu phẩy (,), như tập dữ liệu chúng tôi đã sử dụng trong phần 1, thì bạn có thể sử dụng phương pháp này. Ở đây, hàm RIGHT nhận hai đối số một văn bản và một số vị trí. Sau đó, hàm LEFT nhận hai đối số, một văn bản và một số vị trí. Trả về phần ngoài cùng bên trái của văn bản cho đến số vị trí đã cho. Hàm TÌM KIẾM nhận ba đầu vào, find_text (có thể là một giá trị hoặc một mảng), within_text và start_num. Sau đó, trả về vị trí mà đầu vào find_text khớp với bên trong văn bản within_text. Hơn nữa, bắt đầu tìm kiếm từ vị trí start_num

bước

• Bây giờ công thức để tách tên sẽ là

=LEFT(B5,SEARCH(",", B5)-1)

Đây,

• Hàm TÌM KIẾM tìm kiếm dấu phẩy (,) trong B5 và trả về một giá trị
• Hàm LEFT lấy một văn bản là B5 và lấy đối số vị trí từ giá trị trả về của hàm TÌM KIẾM

• Sau đó, bạn sẽ nhận được tên sinh viên đầu tiên được tách ra

• Bây giờ, Điền xử lý công thức xuống

• Tuy nhiên, công thức để tách các ID sẽ là

=RIGHT(B5,LEN(B5)-SEARCH(",",B5))

Đây,

• Hàm TÌM KIẾM tìm kiếm dấu phẩy (,) trong B5 và trả về một giá trị
• Sau đó, giá trị được trừ khỏi giá trị được trả về từ hàm LEN
• Hàm RIGHT lấy một văn bản là B5 và lấy đối số vị trí từ giá trị trả về của các hàm LEN và SEARCH

• Do đó, bạn sẽ tìm thấy kết quả đầu tiên

• Bây giờ, Điền xử lý công thức xuống

Ghi chú. Nếu bạn có số trước rồi mới nhắn tin, chẳng hạn như 101Steve Morris, thì bạn phải trao đổi công thức.

Đọc thêm. Cách tách số trong Excel bằng công thức (5 cách)

6. Chèn mã VBA để phân tách văn bản và số trong Excel

Bây giờ chúng ta sẽ tách văn bản và số khỏi một ô bằng Macro (Mã VBA). Thực hiện theo các bước liên tiếp để có được kết quả mong muốn

bước

• Trước hết, nhấn Alt + F11. Bạn sẽ mở cửa sổ VBA
• Sau đó vào tùy chọn Insert trên thanh công cụ VBA. Nhấp vào nó. Bạn sẽ nhận được một vài lựa chọn. Chọn mô-đun

• Sau đó, nhấp vào Mô-đun. Bạn sẽ nhận được một cửa sổ Mô-đun mới

• Bây giờ hãy viết đoạn mã sau vào đây

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

Trang web này đã giúp chúng tôi tạo và phát triển mã

• Mã này tạo một hàm mới gọi là SplitText(), hàm này nhận hai đối số, dữ liệu kết hợp và giá trị Boolean (TRUE hoặc FALSE)
• Sau đó, lưu nó dưới dạng “Bảng tính đã bật Excel Macro”
• Tuy nhiên, hãy quay lại trang tính của bạn. Trong cột Tên học sinh, hãy chèn công thức

=SplitText(B4,FALSE)

• Sau đó, bạn sẽ tách được các tên

• Sau đó, trong cột ID sinh viên, hãy chèn công thức

=SplitText(B4,TRUE)

• Sau đó, bạn sẽ tách được ID sinh viên

• Sau đó, bạn sẽ tách được ID sinh viên

Lưu ý đặc biệt.  Có một điểm đặc biệt của chức năng Macro này khiến nó hữu ích hơn các chức năng khác. Nó có thể tách các số và văn bản khỏi dữ liệu trong đó mọi thứ được trộn ngẫu nhiên. Ví dụ: nó có thể tách 101 và Steve Morris khỏi Steve10 M1orris, điều mà các phương pháp khác không thể làm được. Nhìn vào những hình ảnh dưới đây.

Đọc thêm. Cách tách số khỏi văn bản trong Excel VBA (3 phương pháp)

Phần kết luận

Sử dụng các phương pháp này, bạn có thể tách số và văn bản khỏi bất kỳ ô nào trong Excel. Bạn có biết phương pháp nào khác không?