Trích xuất số có 6 chữ số từ chuỗi Excel

Mặc dù Microsoft không cung cấp một công thức hoặc cú pháp trực tiếp để chỉ trích xuất các số từ ô Excel, nhưng chúng tôi có thể kết hợp nhiều công thức Excel để tạo một hàm duy nhất có thể được sử dụng để chỉ trích xuất các số hoặc chữ số từ các ô Excel. Trong bài viết này, chúng tôi sẽ cố gắng trình bày và giải thích chi tiết cách chúng tôi có thể chỉ đưa ra các số từ các ô bằng các công thức phù hợp theo một số tiêu chí

Mục lục

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

Tải xuống sách thực hành miễn phí mà chúng tôi đã sử dụng để chuẩn bị cho bài viết này. Bạn có thể nhập các giá trị văn bản có số vào các ô đã chọn và tìm kết quả ngay lập tức thông qua các công thức được nhúng

Trích xuất số từ ô. xlsm

7 cách hiệu quả để chỉ trích xuất các số từ ô Excel

Sẽ có một mã VBA, một tính năng Excel và năm công thức thực tế để giúp bạn trích xuất các số từ một ô. Giống như trong hình dưới đây, chúng tôi có một số mã bao gồm các chữ số và chữ cái có chữ số ở đầu. Chúng tôi chỉ phải trích xuất các chữ số hoặc số đó

1. Kéo ra các số từ đầu văn bản

Trong phương thức đầu tiên này, chúng ta sẽ kết hợp các hàm LEFT, SUM, LEN và SUBSTITUTE để lấy ra các số từ đầu chuỗi văn bản. Đầu tiên, chúng ta sẽ nhập công thức này vào ô, sau đó, sử dụng Fill Handle, chúng ta sẽ sao chép công thức đó sang các ô còn lại

bước

• Đầu tiên gõ công thức vào ô C5

=LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))))

• Thứ hai, nhấn Enter và bạn sẽ nhận được số 34 cho mã đầu tiên

• Thứ ba, sau đó sử dụng Fill Handle để tự động điền tất cả các ô khác trong cột C

🔎 Phân tích công thức

➤ SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””)

• Ở đây, hàm SUBSTITUTE sẽ tìm các chữ số (0-9) liên tiếp và nếu tìm thấy, nó sẽ thay thế chữ số đó trong ô B5 bằng một ký tự trống mỗi lần. Vì vậy, hàm sẽ trả về dưới dạng- {“34DTXRF”,”34DTXRF”,”34DTXRF”,”4DTXRF”,”3DTXRF”,”34DTXRF”,”34DTXRF”,”34DTXRF”,”34DTXRF”,”34DTXRF”}

➤ LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},”

• Hàm LEN xác định số lượng ký tự trong một chuỗi. Vì vậy, ở đây, hàm LEN sẽ đếm tất cả các ký tự riêng lẻ được tìm thấy trong văn bản thông qua hàm SUBSTITUTE. Các giá trị kết quả sẽ ở đây trong trường hợp của chúng ta – {7,7,7,6,6,7,7,7,7,7}

➤ LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”

• Bây giờ, phần này là phép trừ từ số ký tự trong ô B5 cho tất cả các số ký tự khác được tìm thấy riêng lẻ trong phần trước của công thức. Vì vậy, ở đây các giá trị kết quả sẽ là – {0,0,0,1,1,0,0,0,0,0}

➤ SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″

• Sau đó, hàm SUM sẽ chỉ tính tổng tất cả các giá trị đã trừ được tìm thấy & do đó, kết quả sẽ ở đây, 2 (0+0+0+1+1+0+0+0+0+0)

➤ =LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7

• Và bây giờ, đây là phần cuối cùng trong đó hàm LEFT sẽ trả về các giá trị có số lượng ký tự chính xác từ bên trái được tìm thấy trong phần trước của công thức. Khi chúng ta có giá trị tổng là 2, hàm LEFT ở đây sẽ chỉ trả về 34 từ văn bản 34DTXRF

➥ Liên quan. Cách tách số trong Excel bằng công thức (5 cách)

2. Trích xuất các số từ bên phải của văn bản

Trong phần này, chúng tôi sẽ trích xuất các số hoặc chữ số từ phía bên phải của chuỗi văn bản. Chúng ta sẽ sử dụng các hàm RIGHT, MIN và SEARCH tại đây

bước

• Để bắt đầu, trong tập dữ liệu của chúng tôi, những gì chúng tôi phải nhập vào ô C5 là-

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

• Sau đó, nhấn Enter rồi sử dụng Fill Handle để tự động điền các ô còn lại

🔎 Phân tích công thức

➤ B5&”0123456789″

• Ở đây, chúng tôi đang nối các giá trị trong ô B5 với 0123456789 bằng cách sử dụng dấu và (&) giữa chúng và chúng tôi sẽ nhận được giá trị kết quả là- DTXRF340123456789

➤ TÌM KIẾM({0,1,2,3,4,5,6,7,8,9}, B5&”0123456789″)

• Bây giờ, hàm TÌM KIẾM sẽ lần lượt tìm kiếm tất cả các chữ số (0-9) trong giá trị kết quả thu được từ phần trước và sẽ trả về vị trí của 10 chữ số đó trong các ký tự của DTXRF340123456789. Vì vậy, ở đây các giá trị kết quả của chúng tôi sẽ là- {8,9,10,6,7,13,14,15,16,17}

➤ TỐI THIỂU(TÌM KIẾM({0,1,2,3,4,5,6,7,8,9}, B5&”0123456789″))

• Hàm MIN dùng để tìm chữ số hoặc số nhỏ nhất trong một mảng. Vì vậy, ở đây giá trị tối thiểu hoặc thấp nhất sẽ là- 6 từ mảng {8,9,10,6,7,13,14,15,16,17} được tìm thấy trong phần trước của công thức

➤ LEN(B5) – MIN(TÌM KIẾM({0,1,2,3,4,5,6,7,8,9}, B5&”0123456789″)) +1)

• Bây giờ, số lượng ký tự trong B5 sẽ được tìm thấy bởi hàm LEN. Sau đó, nó sẽ trừ đi giá trị 6 (được tìm thấy trong phần trước) và sau đó trả về kết quả bằng cách thêm 1. Ở đây trong trường hợp của chúng ta, giá trị kết quả sẽ là 2 (7-6+1)

➤ PHẢI(B5,LEN(B5) – TỐI THIỂU(TÌM KIẾM({0,1,2,3,4,5,6,7,8,9}, B5&”0123456789″)) +1)

• Hàm RIGHT sẽ trả về số lượng ký tự được chỉ định từ phía cuối cùng hoặc phía bên phải của chuỗi. Theo kết quả tìm được thông qua quá trình trừ ở phần trước, ở đây hàm RIGHT sẽ hiển thị 2 ký tự cuối cùng từ ô B5 và đó sẽ là 34

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

3. Trích xuất số từ bất kỳ phần nào của chuỗi văn bản

Bây giờ, đây là một giải pháp rộng rãi cho mọi trường hợp. Phương thức này sẽ lấy ra các số hoặc chữ số từ bất kỳ vị trí nào trong chuỗi văn bản. Hơn nữa, chúng ta sẽ sử dụng các hàm TEXTJOIN, IFERROR, INDIRECT, MID và ROW trong phương pháp này

bước

• Đầu tiên, nhập công thức vào ô đích của bạn như sau-

=TEXTJOIN("",TRUE,IFERROR((MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)*1),""))

• Sau đó, nếu bạn đang sử dụng phiên bản Excel 2016 trở lên thì nhấn Enter, nếu không thì nhấn Ctrl+Shift+Enter để lấy kết quả cho công thức mảng này
• Sau bước này, hãy tự động điền các ô khác bằng cách sử dụng Fill Handle và bạn đã hoàn tất

🔎 Phân tích công thức

➤ GIÁN TIẾP(“1. ”&LEN(B5))

• Hàm INDIRECT được sử dụng để lưu trữ một mảng các giá trị ô dưới dạng văn bản tham chiếu. Ở đây, lệnh dấu và (&) nối độ dài của các ký tự của ô B5 với cú pháp phạm vi không đầy đủ (1. )
• Vì vậy, ở đây hàm INDIRECT sẽ lưu trữ tất cả các số từ 1 đến độ dài của các ký tự trong ô B5 dưới dạng văn bản tham chiếu

➤ HÀNG( GIÁN TIẾP(“1. ”&LEN(B5)))

• Hàm ROW thường cho biết số hàng của một ô. Nhưng ở đây trong hàm INDIRECT, vì không có ô tham chiếu nào được đề cập, trong trường hợp này, hàm ROW sẽ trích xuất tất cả các giá trị hoặc số từ các văn bản tham chiếu được lưu trữ trong hàm INDIRECT
• Bây giờ, đối với ô đầu tiên B5, các giá trị kết quả thông qua các hàm ROW và INDIRECT này sẽ là- {1;2;3;4;5;6;7;8;9}

➤ (GIỮA(B5, HÀNG( GIÁN TIẾP(“1. ”&LEN(B5))),1))

• Hàm MID sẽ cho phép bạn xác định các ký tự từ giữa chuỗi văn bản, được cung cấp vị trí bắt đầu và độ dài
• Vì vậy, ở đây đối với tất cả 9 vị trí được tìm thấy trong phần trước, hàm MID giờ đây sẽ hiển thị lần lượt tất cả các ký tự cho từng vị trí và do đó sẽ trả về các giá trị là- {“1″;”9″;”

➤ IFERROR((MID(B5,ROW(INDIRECT(“1. ”&LEN(B5))),1)*1),””)

• Bây giờ, IFERROR là một hàm logic sẽ xác định xem một chuỗi là một số hay một thứ gì khác. Nếu nó không xác định một chuỗi có số hoặc chữ số, thì nó sẽ trả về giá trị bằng một lệnh văn bản đã xác định
• Trong trường hợp của chúng tôi, tất cả các giá trị được tìm thấy trong phần cuối cùng sẽ được nhân với 1 và khi kết quả được trả về dưới dạng lỗi giá trị cho các chữ cái hoặc giá trị văn bản không thể nhân được, hàm IFERROR của chúng sẽ chuyển đổi các lỗi thành các chuỗi trống. Vì vậy, các giá trị kết quả của chúng ta sẽ là- {1;9;””;””;””;””;2;””;””}

➤ =TEXTJOIN(“”,TRUE,IFERROR((MID(B5,ROW(INDIRECT(“1. ”&LEN(B5))),1)*1),””))

• Và bây giờ phần cuối cùng sẽ được thực hiện thông qua hàm TEXTJOIN. Hàm này được sử dụng để nối hoặc nối hai chuỗi với một dấu phân cách được chỉ định
• Vì vậy, các giá trị kết quả mà chúng ta đã tìm thấy trong phần trước giờ sẽ được kết hợp với nhau cùng với hàm TEXTJOIN này. Và do đó, chúng tôi sẽ nhận được số 192

Đọc thêm. Cách trích xuất nhiều số từ chuỗi trong Excel (6 phương pháp)

4. Lồng ghép nhiều hàm để chỉ đưa ra các số

Bây giờ, chúng tôi sẽ chỉ cho bạn một công thức khác để chỉ trích xuất các số từ bất kỳ vị trí nào trong một ô Excel. Mặc dù nó có vẻ khá phức tạp, nhưng chúng tôi sẽ chia nhỏ toàn bộ công thức và cố gắng giải thích tất cả các hàm rút gọn một cách dễ dàng. Ngoài ra, chúng ta sẽ sử dụng các hàm IF, LARGE, INDEX, SUMPRODUCT và ISNUMBER trong công thức này

• Để bắt đầu, hãy nhập công thức này vào ô C5. Bạn chỉ phải thay thế tham chiếu ô dựa trên ô của riêng bạn trong bảng tính và sau đó bằng cách nhúng công thức này, bạn sẽ nhận được kết quả như mong đợi ngay lập tức. Và công thức này hoạt động hoàn hảo trong mọi phiên bản Excel

=IF(SUM(LEN(B5)-LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5,ROW(INDIRECT("$1:$"&LEN(B5))),1))* ROW(INDIRECT("$1:$"&LEN(B5))),0), ROW(INDIRECT("$1:$"&LEN(B5))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(B5)))/10),"")

• Sau đó, bạn chỉ cần nhấn Enter sau khi gõ toàn bộ công thức là xong

🔎 Phân tích công thức

Trước khi bắt đầu phân tích công thức đồ sộ & nhỏ gọn này, chúng ta có thể tách nó thành một số phần như-

=IF(A>0, TÓM TẮT(B1*C1, B2*C2, ………. BnCn),””)

Cú pháp này có nghĩa là nếu A lớn hơn 0 thì tất cả các tích của Bn và Cn sẽ cộng lại thành kết quả cuối cùng. Và nếu A không lớn hơn 0 thì kết quả sẽ trả về là ô trống hoặc trống

• A = SUM(LEN(B5)-LEN(SUBSTITUTE(B5, {“0″,”1″,”2″,”3″,”4″,
• B = MID(0&B5, LARGE(INDEX(ISNUMBER(–MID(B5,ROW(INDIRECT(“$1. $”&LEN(B5))),1))* ROW(INDIRECT(“$1. $”&LEN(B5))),0), ROW(INDIRECT(“$1. $”&LEN(B5))))+1,1)
• C = 10^ROW(INDIRECT(“$1. $”&LEN(B5)))/10),””

Bảng phân tích của Phần A = SUM(LEN(B5)-LEN(SUBSTITUTE(B5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″

➤ SUBSTITUTE(B5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)

• Hàm SUBSTITUTE sẽ lần lượt tìm tất cả các chữ số (0-9) trong văn bản 19 DDX2MN và sẽ thay thế các chữ số đó bằng một chuỗi trống ở vị trí của các chữ số
• Do đó, các giá trị kết quả trong một mảng sẽ là- {“19 DDX2MN”,”9 DDX2MN”,”19 DDXMN”,”19 DDX2MN”,”19 DDX2MN”,”19 DDX2MN”,”19 DDX2MN”,”19 DDX2MN”

➤ LEN(SUBSTITUTE(B5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “

• Hàm LEN bây giờ sẽ đếm số lượng ký tự trong tất cả các giá trị chuỗi thu được từ phần trước. Vì vậy, hàm này sẽ trả về dưới dạng- {9,8,8,9,9,9,9,9,9,8}

➤ LEN(B5)-LEN(SUBSTITUTE(B5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”

• Bây giờ trong phần này của công thức, một số ký tự trong ô B5 sẽ trừ tất cả các số được tìm thấy trong phần trước. Các giá trị kết quả sau đó sẽ là- {0,1,1,0,0,0,0,0,0,1}

➤ SUM(LEN(B5)-LEN(SUBSTITUTE(B5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″

• Với sự trợ giúp của hàm SUM, các giá trị bên trong mảng được tìm thấy trong phần trước sẽ cộng lại thành 3 (0+1+1+0+0+0+0+0+0+1)
• Vì vậy, theo phần đầu tiên của công thức của chúng tôi, A>0 (3>0). Bây giờ, chúng ta sẽ chuyển sang phần tiếp theo của sự cố

Phân tích của Phần B = MID(0&B5, LARGE(INDEX(ISNUMBER(–MID(B5,ROW(INDIRECT(“$1. $”&LEN(B5))),1))* ROW(INDIRECT(“$1. $”&LEN(B5))),0), ROW(INDIRECT(“$1. $”&LEN(B5))))+1,1)

➤ GIÁN TIẾP(“$1. $”&LEN(B5))

• Hàm INDIRECT ở đây sẽ lưu trữ các giá trị chuỗi dưới dạng tham chiếu đến mảng. Bên trong dấu ngoặc đơn, lệnh dấu và (&) sẽ nối số ký tự được tìm thấy trong ô B5 với cú pháp Phạm vi ô. Có nghĩa là từ 1 đến số ký tự được xác định, mỗi ký tự sẽ được lưu dưới dạng tham chiếu mảng

➤ HÀNG( GIÁN TIẾP(“$1. $”&LEN(B5)))

• Bây giờ, hàm ROW này sẽ lấy ra tất cả các số từ mảng và các giá trị kết quả cho ô B5 sẽ là- {1;2;3;4;5;6;7;8;9}

➤ GIỮA(B5,ROW(INDIRECT(“$1. $”&LEN(B5))),1)

• Trong phần này của công thức, hàm MID sẽ biểu thị tất cả các ký tự từ ô B5 dựa trên tất cả các vị trí được tìm thấy dưới dạng số trong phần trước. Vì vậy, các giá trị được trích xuất sẽ được tìm thấy sau phần này- {“1″;”9″;”

➤ ISNUMBER(–MID(B5,ROW(INDIRECT(“$1. $”&LEN(B5))),1))

• Vì ISNUMBER là một hàm logic nên nó sẽ xác định riêng lẻ xem các giá trị được tìm thấy trong phần trước có phải là chuỗi số hay không. Nếu có, thì nó sẽ trả về giá trị TRUE, nếu không, nó sẽ hiển thị là FALSE
• Vì vậy, trong trường hợp của chúng ta, kết quả sẽ là- {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

➤ CHỈ SỐ(ISNUMBER(–MID(B5,ROW(INDIRECT(“$1. $”&LEN(B5))),1))*ROW(INDIRECT(“$1. $”&LEN(B5))),0)

• Nếu bạn để ý bên trong hàm trên, một dấu gạch ngang kép, được gọi là Double Unary, đã được sử dụng. Nó được sử dụng để chuyển đổi tất cả các giá trị logic thành chuỗi số- 1(TRUE) hoặc 0(FALSE). Bây giờ, hàm INDEX sẽ trả về kết quả này là- {1;1;0;0;0;0;1;0;0}
• Sau đó, các giá trị kết quả sẽ được nhân với các giá trị thu được từ hàm ROW bên trong mảng và kết quả sẽ là- {1;2;0;0;0;0;7;0;0}

➤ LỚN(CHỈ SỐ(ISNUMBER(–MID(B5,ROW(INDIRECT(“$1. $”&LEN(B5))),1))*ROW(INDIRECT(“$1. $”&LEN(B5))),0), ROW(INDIRECT(“$1. $”&LEN(B5))))

• Hàm LARGE bây giờ sẽ sắp xếp lại các giá trị lớn nhất từ ​​mảng theo vị trí dựa trên các số được tìm thấy trong hàm ROW. & các giá trị kết quả của chúng tôi cho phần này của công thức sẽ là- {7;2;1;0;0;0;0;0;0}

➤ TRUNG BÌNH(0&B5, LỚN(CHỈ SỐ(ISNUMBER(–MID(B5,ROW(INDIRECT(“$1. $”&LEN(B5))),1))*ROW(INDIRECT(“$1. $”&LEN(B5))),0), ROW(INDIRECT(“$1. $”&LEN(B5))))+1,1)

• Bây giờ, phần này của hàm sẽ nối 0 với các văn bản trong ô B5. Sau đó, nó sẽ thêm 1 riêng lẻ với tất cả các số được tìm thấy trong phần trước và hiển thị các ký tự từ ô B5 dựa trên các vị trí số đã xác định
• Vì vậy, kết quả của chúng tôi từ phần này sẽ là- {“2″;”9″;”1″;”0″;”0″;”0″;”0″;”0″;”0”}

Phân tích của Phần C = (10^ROW(INDIRECT(“$1. $”&LEN(B5)))/10),””)

• Phần này sẽ xác định lũy thừa của 10 và lưu trữ chúng bên trong mảng. Các chữ số của lũy thừa là các số tìm được từ hàm ROW trước đó
• Phần này của công thức sẽ trả về các giá trị như- {1;10;100;1000;10000;100000;1000000;10000000;100000000}

Nhân của Bn và Cn

• Bây giờ, các giá trị kết quả từ hai lần chia nhỏ cuối cùng của B và C sẽ được nhân lên bên trong mảng. Sau đó, các sản phẩm được tìm thấy từ các phép nhân sẽ là- {2;90;100;0;0;0;0;0;0}
• Và cuối cùng, hàm SUMPRODUCT sẽ tính tổng các giá trị này được tìm thấy trong mảng. Vì vậy, kết quả cuối cùng của chúng ta sẽ là 192 (2+90+100+0+0+0+0+0+0), là các số được trích xuất từ ​​ô B5

Đọc thêm. Cách tách văn bản và số trong Excel (4 cách dễ dàng)

5. Trích xuất năm chữ số từ chuỗi

Chúng tôi sẽ sử dụng một công thức khác để trích xuất các số có năm chữ số từ bất kỳ phần nào của chuỗi trong Excel. Chúng ta sẽ sử dụng các hàm CONCAT và SEQUENCE lần đầu tiên trong phần này. Hơn nữa, chúng tôi đã thay đổi một chút tập dữ liệu của mình cho phương pháp này

bước

• Đầu tiên, chọn dãy ô C5. C12
• Thứ hai, gõ công thức sau

________số 8_______

• Cuối cùng, nhấn Ctrl + Enter

🔎 Phân tích công thức

• LEN(B5)
  • Đầu ra. 11 .
  • Hàm này trả về độ dài của chuỗi
• TRÌNH TỰ(11)
  • Đầu ra. {1;2;3;4;5;6;7;8;9;10;11} .
  • Hàm này trả về mười một số đầu tiên
• MID(B5,{1;2;3;4;5;6;7;8;9;10;11},1)
  • Đầu ra. {“1″;”9″;” . .
  • Sử dụng phần này, chúng tôi sẽ nhận được các ký tự riêng lẻ từ chuỗi
• 0+{“1″;”9″;”
  • Đầu ra. {1;9;#VALUE. ;#GIÁ TRỊ. ;#GIÁ TRỊ. ;#GIÁ TRỊ. ;2;#VALUE. ;#GIÁ TRỊ. ;3;3} .
  • Khi chúng ta thêm số 0 vào một chuỗi, nó sẽ trả về lỗi
• IFERROR({1;9;#VALUE. ;#GIÁ TRỊ. ;#GIÁ TRỊ. ;#GIÁ TRỊ. ;2;#VALUE. ;#GIÁ TRỊ. ;3;3},””)
  • Đầu ra. {1;9;””;””;””;””;2;””;””;3;3} .
  • Chúng tôi đang để trống cho tất cả các giá trị lỗi
• CONCAT({1;9;””;””;””;””;2;””;””;3;3})
  • Đầu ra. 19233 .
  • Cuối cùng, chúng tôi đang thêm tất cả các giá trị để chỉ trích xuất các số có năm chữ số

6. Sử dụng Flash Fill để trích xuất số trong một phạm vi

Sử dụng tính năng Flash Fill dễ dàng và đơn giản hơn bất kỳ phương pháp nào khác được đề cập ở trên. Chúng tôi sẽ trích xuất các số từ bất kỳ vị trí nào trong chuỗi văn bản. Để thực hiện đúng phương pháp này, chúng ta phải giúp Excel tìm mẫu của các giá trị ô trong một cột hoặc một hàng bằng cách chỉ thực hiện trích xuất hai giá trị đầu tiên

bước

• Để bắt đầu, hãy nhập các số theo cách thủ công vào ô C5

• Sau đó, bắt đầu nhập các số từ ô B6 đến ô C6 và Excel sẽ tự động nhận dạng mẫu
• Cuối cùng nhấn Enter

Ghi chú. Phương pháp này có một số nhược điểm, đó là lý do tại sao nó không được khuyến nghị cho mọi trường hợp khi bạn cần trích xuất số từ chuỗi văn bản. Flash Fill thường theo một mẫu từ các ô trong một cột hoặc một phạm vi. Vì vậy, 2 hoặc 3 lần trích xuất hoặc tính toán đầu tiên phải được thực hiện thủ công để giúp Excel tiếp thu mẫu chung của các giá trị kết quả. Nhưng đôi khi, nó không tuân theo chính xác mẫu mà chúng ta cần và do đó, nó sẽ theo mẫu của chính nó và cho bạn kết quả không khớp.

Ví dụ: nếu chúng tôi phải trích xuất hai số không (00) từ dữ liệu đã cho, nó sẽ chỉ hiển thị một số không, không phải hai. Sau đó, nếu bạn muốn trích xuất các số từ vị trí đầu hoặc cuối trong một ô, nó cũng sẽ trích xuất các giá trị văn bản cùng với các số

Đọc thêm. Cách trích xuất số sau một văn bản cụ thể trong Excel (2 cách phù hợp)

7. Áp dụng mã VBA để chỉ trích xuất các số từ ô Excel

Nếu bạn quan tâm đến việc sử dụng Macro VBA của Excel để chỉ trích xuất các số từ các ô, thì bạn muốn làm theo các bước bên dưới. Chúng tôi sẽ hướng dẫn bạn cách nhập mã trong cửa sổ Mô-đun VBA. Mã này sẽ yêu cầu người dùng chỉ định phạm vi ô đầu vào và đầu ra

bước

• Đầu tiên, Nhấn ALT+F11 để mở cửa sổ VBA
• Sau đó, từ tab Chèn, chọn lệnh Mô-đun. Một cửa sổ mô-đun mới sẽ xuất hiện nơi bạn sẽ nhập mã

• Thứ ba, bên trong mô-đun của bạn, dán các mã sau sau khi sao chép

Option Explicit
Sub ExtractNumbersOnly()
    Dim CellValue As Range
    Dim InBx1 As Range
    Dim InBx2 As Range
    Dim NumChar As Integer
    Dim StartChar As Integer
    Dim XtrNum As String
    Dim DBxName1 As String
    Dim DBxName2 As String
    Dim Iteration As Integer
    DBxName1 = "Input Data Selection"
    DBxName2 = "Output Cell Selection"
    Set InBx1 = Application.InputBox("Input Range of Text Cells:", _
    DBxName1, "", Type:=8)
    If TypeName(InBx1) = "Nothing" Then Exit Sub
    Set InBx2 = Application.InputBox("Select Output Cells:", _
    DBxName2, "", Type:=8)
    If TypeName(InBx2) = "Nothing" Then Exit Sub
    Iteration = 0
    XtrNum = ""
    For Each CellValue In InBx1
        Iteration = Iteration + 1
        NumChar = Len(CellValue)
        For StartChar = 1 To NumChar
            If IsNumeric(Mid(CellValue, StartChar, 1)) Then
            XtrNum = XtrNum & Mid(CellValue, StartChar, 1)
            End If
        Next StartChar
    InBx2.Item(Iteration) = XtrNum
    XtrNum = ""
    Next CellValue
End Sub

• Sau đó nhấn F5 để thực thi code. Một hộp thoại có tên “Lựa chọn dữ liệu đầu vào” sẽ xuất hiện
• Sau đó, chọn tất cả các ô văn bản (i. e. B5. B12) và nhấn OK

• Sau đó, một hộp thoại khác có tên “Lựa chọn ô đầu ra” sẽ xuất hiện trong đó bạn phải chọn một ô hoặc dải ô cụ thể để xem dữ liệu hoặc giá trị đầu ra
• Cuối cùng, chọn dãy ô C5. C12 và nhấn Enter

• Do đó, bạn sẽ thấy tất cả các số được trích xuất từ ​​​​các văn bản cùng một lúc. Như vậy là chúng ta đã hoàn thành xong 7 phương pháp trích số nhanh chỉ trong ô Excel

🔎 Phân tích mã VBA

➤ Khai báo tham số

Option Explicit
Sub ExtractNumbersOnly()
    Dim cellValue As Range
    Dim InBx1 As Range
    Dim InBx2 As Range
    Dim NumChar As Integer
    Dim StartChar As Integer
    Dim XtrNum As String
    Dim DBxName1 As String
    Dim DBxName2 As String
    Dim Iteration As Integer
    DBxName1 = "Input Data Selection"
    DBxName2 = "Output cell Selection"

• Đầu tiên, trong phần này, chúng tôi khai báo tất cả các tham số của mình dưới dạng số nguyên, giá trị chuỗi hoặc phạm vi ô. Sau đó, chúng tôi sẽ đặt tên cho các hộp thoại của mình với “Lựa chọn dữ liệu đầu vào” và “Lựa chọn ô đầu ra”

➤ Xác định các loại Đầu vào & Đầu ra cho Hộp thoại

    Set InBx1 = Application.InputBox("Input Range of Text Cells:", _
    DBxName1, "", Type:=8)
    If TypeName(InBx1) = "Nothing" Then Exit Sub
    Set InBx2 = Application.InputBox("Select Output Cells:", _
    DBxName2, "", Type:=8)
    If TypeName(InBx2) = "Nothing" Then Exit Sub
    Iteration = 0
    XtrNum = ""

• Bây giờ chúng tôi đang xác định các tham số và loại của chúng cho các hộp thoại. Ở đây, thêm Loại. =8 có nghĩa là dữ liệu đầu vào và đầu ra sẽ bao gồm các ô tham chiếu hoặc một dải ô
• Chúng tôi cũng xác định rằng nếu không tìm thấy dữ liệu đầu vào, thì chương trình con sẽ dừng. Bằng cách đề cập đến macro này, chương trình con sẽ không bị hỏng do thiếu dữ liệu mà thay vào đó, nó sẽ ngừng hoạt động

➤ Kết hợp các chức năng bên trong các vòng lặp mã để lặp lại

For Each cellValue In InBx1
        Iteration = Iteration + 1
        NumChar = Len(cellValue)
        For StartChar = 1 To NumChar
            If IsNumeric(Mid(cellValue, StartChar, 1)) Then
            XtrNum = XtrNum & Mid(cellValue, StartChar, 1)
            End If
        Next StartChar
    InBx2.Item(Iteration) = XtrNum
    XtrNum = ""
    Next cellValue
End Sub

• Cuối cùng, đây là phần quan trọng nhất mà chúng tôi đang áp dụng các hàm hoặc công thức mà chúng tôi cần gán cho văn bản để tìm các giá trị kết quả từ các chuỗi
• Một trong những lợi thế chính của việc mã hóa một hàm cho Excel là không cần phải nhập một công thức lớn như chúng ta phải làm trong các phương thức trước đây, vì VBA có sẵn các lệnh để sử dụng các vòng lặp For hoặc While trong đó lặp lại cho từng chi tiết.

Đọ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

Chúng tôi đã chỉ cho bạn 7 phương pháp dễ dàng để chỉ trích xuất các số từ một ô Excel. Chỉ trích xuất các số từ một chuỗi văn bản không đơn giản như vẻ ngoài của nó vì nó yêu cầu kết hợp nhiều hàm, điều này làm cho công thức hoặc cú pháp cuối cùng trở nên phức tạp. Nhưng chúng tôi hy vọng cách chúng tôi cố gắng minh họa các công thức bằng cách chia nhỏ các hàm bên trong đã giúp bạn hiểu cú pháp một cách thoải mái và dễ dàng

Nếu bạn tìm thấy bất kỳ chức năng hoặc công thức nào khác mà chúng tôi nên thêm vào đây, vui lòng cho chúng tôi biết thông qua các nhận xét có giá trị của bạn. Hoặc bạn có thể xem qua các bài viết thú vị và nhiều thông tin hơn của chúng tôi liên quan đến các hàm Excel trên trang web này