Không có chức năng tích hợp nào trong Excel để trích xuất số từ một chuỗi trong một ô (và ngược lại - loại bỏ phần số và trích xuất phần văn bản từ một chuỗi chữ và số).
Tuy nhiên, điều này có thể được thực hiện bằng cách sử dụng một hàm Excel hoặc một số mã VBA đơn giản.
Đầu tiên hãy để tôi nói cho bạn biết tôi đang nói về điều gì.
Giả sử bạn có một tập dữ liệu như bên dưới và bạn muốn trích xuất các số từ các chuỗi (như bên dưới):
Phương pháp bạn chọn cũng phụ thuộc vào phiên bản Excel bạn đang sử dụng:
- Đối với các phiên bản Excel trước năm 2016, bạn cần sử dụng công thức dài hơn một chút
- Đối với Excel 2016, bạn có thể sử dụng hàm TEXTJOIN mới được giới thiệu
- Phương pháp VBA có sẵn cho tất cả các phiên bản Excel
Nội dung
Trích xuất số từ chuỗi trong Excel (công thức Excel 2016)
Công thức này 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 chuỗi văn bản.
Lưu ý rằng công thức TEXTJOIN được mô tả trong phần này đặt tất cả các ký tự số lại với nhau.Ví dụ: nếu văn bản là "10 vé là 200 đô la", kết quả là 10200.
Giả sử bạn có một tập dữ liệu như sau và bạn muốn trích xuất các số từ các chuỗi trong mỗi ô:
Công thức dưới đây sẽ cung cấp cho bạn phần số của 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 " Điều khiển + Shift + Enter "thay vì sử dụng Enter.
Công thức này sẽ trả về giá trị trống (chuỗi trống) nếu không có số nào trong chuỗi văn bản.
Công thức này hoạt động như thế nào?
Hãy để tôi chia nhỏ công thức này và cố gắng giải thích cách nó hoạt động:
- ROW (INDIRECT (“1:” & LEN (A2))) - Phần này của công thức sẽ cung cấp một loạt các số bắt đầu từ một.Hàm LEN trong công thức trả về tổng số ký tự trong chuỗi.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1) * 1) - Phần này của công thức sẽ trả về một mảng #VALUE!Các lỗi hoặc số dựa trên chuỗi.Tất cả các ký tự văn bản trong chuỗi trở thành #VALUE!Các lỗi và tất cả các giá trị vẫn nguyên như cũ.Điều này xảy ra khi chúng ta nhân hàm MID với 1.
- IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1) * 1), ””) - Khi sử dụng hàm IFERROR, tất cả các lỗi #VALUE đều bị xóa!Lỗi, chỉ số sẽ còn lại.Kết quả của phần này có dạng như sau - {""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; " ";" ";" "; 1; 0; 0}
- = TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1) * 1), "")) - Hàm TEXTJOIN bây giờ chỉ đơn giản là kết hợp các ký tự chuỗi Vẫn còn đó (chỉ số) và bỏ qua các chuỗi trống.
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 bạn muốn xuất và nhấn F9.Bạn sẽ thấy ngay kết quả.Sau đó, hãy nhớ nhấn Control + Z hoặc nhấn Escape.Không nhấn enter.
Bạn cũng có thể sử dụng logic tương tự để trích xuất các phần của văn bản từ các chuỗi chữ và số.Đây là công thức để 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ỏ đối với công thức này là hàm IF được sử dụng để kiểm tra mảng mà chúng tôi nhận được từ hàm MID để tìm lỗi.Nếu là lỗi, hãy giữ nguyên giá trị, nếu không thì thay thế bằng giá trị trống.
Sau đó, sử dụng TEXTJOIN để kết hợp tất cả các ký tự văn bản.
注意: Mặc dù công thức này hợp lệ, 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à nếu bạn đang sử dụng nó với một tập dữ liệu khổng lồ, có thể mất một khoảng 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 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 phải sử dụng các 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ư bên dưới và bạn muốn trích xuất tất cả các số dưới dạng chuỗi trong mỗi ô.
Công thức dưới đây sẽ thực hiện điều 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),"")
Công thức này sẽ trả về giá trị trống (chuỗi trống) nếu không có số nào trong chuỗi văn bản.
Mặc dù đây là một công thức mảng, bạnKhông cầnSử dụng "Control-Shift-Enter" để sử dụng nó.Một đầu vào đơn giản phù hợp với công thức này.
Tín dụng cho công thức này là điều đáng kinh ngạcDiễn đàn Excel Mr..
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à "10 vé là 200 đô la", kết quả là 10200.
注意: Mặc dù công thức này hợp lệ, 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à nếu bạn đang sử dụng nó với một tập dữ liệu khổng lồ, có thể mất một khoảng 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 sử dụng công thức này trong Excel.
Tách văn bản và số trong Excel bằng VBA
Nếu bạn thường xuyên cần tách văn bản và số (hoặc trích xuất số khỏi văn bả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à tạo một hàm tùy chỉnh do người dùng xác định (UDF) trong Excel bằng mã VBA đơn giản, sau đó sử dụng công thức VBA đó thay vì một công thức dài và phức tạp.
Hãy để 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ừ 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 một hàm tùy chỉnh để chỉ lấy phần số từ một chuỗ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:
Hàm 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 = Hàm kết thúc kết quả
Dưới đây là các bước để tạo hàm này và sau đó sử dụng nó trong một trang tính:
Bây giờ bạn sẽ có thể sử dụng chức năng GetText trên trang tính.Vì chúng tôi đã thực hiện tất cả các thao tác nặng nhọc trong chính mã, nên 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 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 chứa mã VBA, bạn cần lưu nó bằng phần mở rộng .xls hoặc .xlsm.
Nếu 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 nó 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 bạn sử dụng.
Trích xuất văn bản 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 một hàm tùy chỉnh để chỉ lấy phần văn bản từ một chuỗ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:
Hàm 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 ) Tiếp theo tôi GetText = Hàm Kết thúc Kết quả
Dưới đây là các bước để tạo hàm này và sau đó sử dụng nó trong một 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 thao tác nặng nhọc trong chính 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 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 chứa mã VBA, bạn cần lưu nó bằng phần mở rộng .xls hoặc .xlsm.
Nếu 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 nó 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 bạn sử dụng.
