Không có hàm sẵn có nào trong Excel để trích xuất các số từ một chuỗi trong một ô (hoặc ngược lại – xóa phần số và trích xuất phần văn bản từ một chuỗi chữ và số) Show
Tuy nhiên, điều này có thể được thực hiện bằng cách sử dụng một loạt các hàm Excel hoặc một số mã VBA đơn giản Trước tiên hãy để tôi chỉ cho bạn những gì tôi đang nói về Giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn trích xuất các số từ chuỗi (như hình bên dưới) Phương pháp bạn chọn cũng sẽ phụ thuộc vào phiên bản Excel bạn đang sử dụng
Hướng dẫn này bao gồm Nhấn vào đây để tải về tập tin ví dụ Trích xuất số từ chuỗi trong Excel (Công thức cho Excel 2016)Công thức này sẽ chỉ hoạt động trong Excel 2016 vì nó sử dụng hàm TEXTJOIN mới được giới thiệu Ngoài ra, công thức này có thể trích xuất các số ở đầu, cuối hoặc giữa của chuỗi văn bản Lưu ý rằng công thức TEXTJOIN được đề cập trong phần này sẽ cung cấp cho bạn tất cả các ký tự số cùng nhau. Ví dụ: nếu văn bản là “Giá của 10 vé là 200 USD”, kết quả sẽ cho bạn 10200 Giả sử bạn có tập dữ liệu như hình bên dưới và bạn muốn trích xuất các số từ các chuỗi trong mỗi ô Dưới đây là công thức sẽ cung cấp cho bạn phần số từ một chuỗi trong Excel =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))Đây là một công thức mảng, vì vậy bạn cần sử dụng ‘ Control + Shift + Enter ‘ thay vì sử dụng Enter Trong trường hợp không có số nào trong chuỗi văn bản, công thức này sẽ trả về một chuỗi trống (trống) Công thức này hoạt động như thế nào? Hãy để tôi phá vỡ công thức này và thử giải thích nó hoạt động như thế nào
Mẹo chuyên nghiệp. Nếu bạn muốn kiểm tra đầu ra của một phần công thức, hãy chọn ô, nhấn F2 để vào chế độ chỉnh sửa, chọn phần công thức mà bạn muốn đầu ra và nhấn F9. Bạn sẽ thấy ngay kết quả. Và sau đó nhớ nhấn Control + Z hoặc nhấn phím Escape. KHÔNG nhấn phím enter Tải xuống tệp ví dụ Bạn cũng có thể sử dụng logic tương tự để trích xuất phần văn bản từ một chuỗi chữ và số. Dưới đây là công thức sẽ lấy phần văn bản từ chuỗi =TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))Một thay đổi nhỏ trong công thức này là hàm IF được dùng để kiểm tra xem mảng ta lấy được từ hàm MID có bị lỗi hay không. Nếu đó là một lỗi, nó sẽ giữ nguyên giá trị, nếu không nó sẽ thay thế nó bằng một khoảng trống Sau đó, TEXTJOIN được sử dụng để kết hợp tất cả các ký tự văn bản thận trọng. Mặc dù công thức này hoạt động tốt, nhưng nó sử dụng một hàm dễ bay hơi (hàm INDIRECT). Điều này có nghĩa là trong trường hợp bạn sử dụng điều này với một tập dữ liệu khổng lồ, có thể mất một thời gian để cung cấp cho bạn kết quả. Tốt nhất là tạo một bản sao lưu trước khi bạn sử dụng công thức này trong Excel Trích xuất số từ chuỗi trong Excel (dành cho Excel 2013/2010/2007)Nếu bạn có Excel 2013. 2010. hoặc 2007, bạn không thể sử dụng công thức TEXTJOIN, vì vậy bạn sẽ phải sử dụng một công thức phức tạp để thực hiện việc này Giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn trích xuất tất cả các số trong chuỗi trong mỗi ô Công thức dưới đây sẽ hoàn thành việc này =IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")Trong trường hợp không có số trong chuỗi văn bản, công thức này sẽ trả về giá trị trống (chuỗi rỗng) Mặc dù đây là công thức mảng nhưng bạn không cần sử dụng 'Control-Shift-Enter' để sử dụng công thức này. Một đầu vào đơn giản hoạt động cho công thức này Tín dụng cho công thức này thuộc về Mr tuyệt vời. diễn đàn excel Một lần nữa, công thức này sẽ trích xuất tất cả các số trong chuỗi bất kể vị trí. Ví dụ: nếu văn bản là “Giá của 10 vé là 200 USD”, kết quả sẽ cho bạn 10200 thận trọng. Mặc dù công thức này hoạt động tốt, nhưng nó sử dụng một hàm dễ bay hơi (hàm INDIRECT). Điều này có nghĩa là trong trường hợp bạn sử dụng điều này với một tập dữ liệu khổng lồ, có thể mất một thời gian để cung cấp cho bạn kết quả. Tốt nhất là tạo một bản sao lưu trước khi bạn sử dụng công thức này trong Excel Tách văn bản và số trong Excel bằng VBANếu việc tách văn bản và số (hoặc trích xuất số từ văn bản) là việc bạn phải làm thường xuyên, bạn cũng có thể sử dụng phương pháp VBA Tất cả những gì bạn cần làm là sử dụng mã VBA đơn giản để tạo Hàm do người dùng xác định (UDF) tùy chỉnh trong Excel, sau đó thay vì sử dụng các công thức dài và phức tạp, hãy sử dụng công thức VBA đó Để tôi chỉ cho bạn cách tạo hai công thức trong VBA – một để trích xuất số và một để trích xuất văn bản từ một chuỗi Trích xuất số từ chuỗi trong Excel (sử dụng VBA)Trong phần này, tôi sẽ chỉ cho bạn cách tạo hàm tùy chỉnh để chỉ lấy phần số từ một chuỗi Dưới đây là mã VBA chúng tôi sẽ sử dụng để tạo chức năng tùy chỉnh này Function GetNumeric(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 GetNumeric = Result End Function Dưới đây là các bước để tạo chức năng này và sau đó sử dụng nó trong trang tính
Bây giờ, bạn sẽ có thể sử dụng hàm GetText trong trang tính. Vì chúng tôi đã thực hiện tất cả các công việc nặng nhọc trong mã, tất cả những gì bạn cần làm là sử dụng công thức =GetNumeric(A2). Điều này sẽ ngay lập tức chỉ cung cấp cho bạn phần số của chuỗi Lưu ý rằng vì sổ làm việc hiện có mã VBA trong đó, bạn cần lưu nó với. xls hoặc. phần mở rộng xlsm Tải xuống tệp ví dụ Trong trường hợp bạn phải sử dụng công thức này thường xuyên, bạn cũng có thể lưu công thức này vào Sổ làm việc Macro cá nhân của mình. Điều này sẽ cho phép bạn sử dụng công thức tùy chỉnh này trong bất kỳ sổ làm việc Excel nào mà bạn làm việc cùng Trích xuất văn bản từ một chuỗi trong Excel (sử dụng VBA)Trong phần này, tôi sẽ chỉ cho bạn cách tạo hàm tùy chỉnh để chỉ lấy phần văn bản từ một chuỗi Dưới đây là mã VBA chúng tôi sẽ sử dụng để tạo chức năng tùy chỉnh này 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 Dưới đây là các bước để tạo chức năng này và sau đó sử dụng nó trong trang tính
Bây giờ, bạn sẽ có thể sử dụng hàm GetNumeric trong trang tính. Vì chúng tôi đã thực hiện tất cả các công việc nặng nhọc trong mã nên tất cả những gì bạn cần làm là sử dụng công thức =GetText(A2) . Điều này sẽ ngay lập tức chỉ cung cấp cho bạn phần số của chuỗi Lưu ý rằng vì sổ làm việc hiện có mã VBA trong đó, bạn cần lưu nó với. xls hoặc. phần mở rộng xlsm Trong trường hợp bạn phải sử dụng công thức này thường xuyên, bạn cũng có thể lưu công thức này vào Sổ làm việc Macro cá nhân của mình. Điều này sẽ cho phép bạn sử dụng công thức tùy chỉnh này trong bất kỳ sổ làm việc Excel nào mà bạn làm việc cùng Trong trường hợp bạn đang sử dụng Excel 2013 hoặc các phiên bản trước và không có Bạn cũng có thể thích các Hướng dẫn Excel sau
SÁCH EXCEL MIỄN PHÍ Nhận Ebook 51 mẹo Excel để tăng năng suất và hoàn thành công việc nhanh hơn
Tên CÓ - GỬI SÁCH ĐIỆN TỬ CHO TÔI 14 suy nghĩ về "Trích xuất số từ chuỗi trong Excel (Sử dụng công thức hoặc VBA)"
|
