Hàm MID trong Excel được dùng để cắt chuỗi ký tự ở giữa xâu ký tự cho trước. Hiện nay, nó được sử dụng khá phổ biến trong quá trình tính toán và thống kê trên bảng tính Excel.
Bài viết dưới đây, Học Excel Online sẽ giúp bạn hiểu rõ hơn về cú pháp và cách sử dụng hàm MID.
Cú pháp của hàm MID
=MID(text,m,n)
– Trong đó:
Bạn đang đọc: Hàm MID và cách sử dụng hàm MID trong Excel
+ text: Chuỗi ký tự.
+ m: Vị trí bắt đầu cần cắt chuỗi ký tự.
+ n: Số ký tự cần cắt ra từ chuỗi ký tự.
Ví dụ
1/ Lấy ra 5 ký tự trong chuỗi đã cho bắt đầu từ vị trí số4 với công thức: =MID(B3;4;5).
=> Kết quả :
2/ Tại cột F, lấy ra 1 ký tự, bắt đầu từ vị trí số 4 trong các ô thuộc cột Mã hộ trong bảng số liệu dưới đây.
=> Kết quả :
Những điều điều cần lưu ý về hàm Excel MID
Khi sử dụng hàm MID các bạn không hề hấp tấp vội vàng và cần phải ghi nhớ 1 số ít điều để tránh việc mắc phải những lỗi sai phổ cập .
- Hàm MID luôn trả về một chuỗi văn bản, ngay cả khi chuỗi văn bản đó chỉ chứa toàn các chữ số. Điều này rất đáng lưu ý nếu bạn muốn sử dụng kết quả của phép tính sử dụng hàm MID trong các phép tính khác. Để chuyển đổi đầu ra của hàm MID là một số thì cần kết hợp hàm MID với hàm VALUE.
- Nếu start_num lớn hơn tổng chiều dài của văn bản gốc, công thức tính theo hàm MID sẽ trả về một chuỗi rỗng (“”).
- Nếu start_num nhỏ hơn 1, công thức tính của hàm MID trả về sẽ bị lỗi #VALUE.
- Nếu num_chars nhỏ hơn 0 (số âm), công thức MID trả về #VALUE! Nếu num_chars bằng 0 sẽ trả về một chuỗi rỗng (ô rỗng).
- Nếu tổng của start_num và num_chars vượt quá tổng chiều dài của chuối văn bản gốc, hàm Excel MID sẽ trả về một chuỗi ký tự bắt đầu từ start_num cho đến ký tự cuối cùng trong chuỗi văn bản gốc đó.
Các ví dụ sử dụng công thức tính của hàm MID
Khi sử dụng hàm MID trong Excel bạn thường sẽ phải phối hợp nó với những hàm khác, như những ví dụ sau đây .
Cách xuất tên và họ
Chúng ta hoàn toàn có thể trích xuất họ, tên lần lượt bằng cách sử dụng hàm LEFT và hàm RIGHT. Tuy nhiên, tất cả chúng ta cũng hoàn toàn có thể thực thi được bằng giải pháp khác .
Sử dụng hàm MID để trích xuất họ
Giả sử, họ và tên rất đầy đủ nằm trong ô A2, họ và tên được phân làn với nhau bằng dấu cách, bạn hoàn toàn có thể trích xuất họ bằng cách sử dụng công thức sau :
=MID(A2,1,SEARCH(” “,A2)-1)
Chức năng SEARCH được dùng để bỏ dấu cách và trả lại vị trí của nó, vì vậy bạn hoàn toàn có thể bỏ lỡ dấu cách đó. Sau đó, bạn dùng hàm MID để trích ra chuỗi con mở màn từ ký tự tiên phong cho đến ký tự đứng trước dấu cách, từ đó ta thu được họ .
Sử dụng hàm MID để trích xuất tên
Để trích xuất tên từ ô A2, các bạn sử dụng công thức sau :
=TRIM(MID(A2,SEARCH(” “,A2),LEN(A2)))
Lần này, SEARCH được sử dụng để xác lập vị trí khởi đầu ( từ chỗ dấu cách ). Không cần tính đúng mực vị trí kết thúc ( vì nếu start_num và num_chars cộng lại lớn hơn tổng chiều dài chuỗi thì hàm sẽ trả về toàn bộ các ký tự còn lại trong chuỗi văn bản gốc ). Vì vậy, trong đối số num_chars, bạn chỉ cần cung ứng tổng chiều dài của chuỗi khởi đầu được trả về bằng cách sử dụng hàm công dụng LEN. Nếu không dùng hàm LEN thì bạn hoàn toàn có thể đặt một số ít thật dài cho chuỗi ký tự cần trích xuất, ví dụ như 100. Cuối cùng hàm TRIM sẽ vô hiệu phần dấu cách và cho ra hiệu quả như sau :
Cách trích xuất chuỗi ký tự nằm giữa hai dấu cách
Như ví dụ ở bên trên, nếu ngoài họ và tên còn chứa tên đệm thì làm thế nào mới trích xuất được tên đệm đó?
Bạn có thể thực hiện theo phương pháp sau đây:
Giống như ở ví dụ trước, chúng ta vẫn sử dụng hàm SEARCH để xác định vị trí của dấu cách đầu tiên, cộng thêm 1 vào đó để chuỗi ký tự trích ra bắt đầu bằng ký tự đứng ngay sau dấu cách. Từ đó, bạn sẽ có được tham số start_num từ công thức MID: SEARCH(” “,A2)+1
Tiếp theo, để có được vị trí của khoảng cách thứ 2 thì chúng ta sử dụng hàm SEARCH với việc bắt đầu tìm kiếm từ ký tự đầu tiên sau dấu cách thứ 2: SEARCH(” “,A2,SEARCH(” “,A2)+1)
Để có được chuỗi ký tự trả về, cần trừ đi vị trí 2 khoảng không của 2 dấu cách. Từ đó, chúng ta có đối số num_chars: SEARCH (” “, A2, SEARCH (” “,A2)+1) – SEARCH (” “,A2)
Kết hợp lại với nhau chúng ta có được công thức MID để trích ra chuỗi ký tự đứng giữa hai khoảng không gian tạo bởi hai dấu cách:
=MID(A2, SEARCH(” “,A2)+1, SEARCH (” “, A2, SEARCH (” “,A2)+1) – SEARCH (” “,A2)-1)
Các bạn hoàn toàn có thể xem hiệu quả trong hình ảnh bên dưới :
Tương tự, bạn hoàn toàn có thể trích xuất một chuỗi con nằm giữa hai dấu cách bất kể :
MID(string, SEARCH(delimiter, string)+1, SEARCH (delimiter, string, SEARCH (delimiter, string)+1) – SEARCH (delimiter, string)-1)
Ví dụ, để trích ra một chuỗi con được ngăn cách bằng dấu phẩy và khoảng cách, bạn sử dụng công thức sau :
=MID(A2,SEARCH(“, “,A2)+1,SEARCH(“, “,A2,SEARCH(“, “,A2)+1)-SEARCH(“, “,A2)-1)
Xem hình bên dưới để thấy sự hiệu quả của công thức này:
Cách trích xuất từ thứ N trong một chuỗi văn bản
Ví dụ này là một ví dụ điển hình cho việc kết hợp sáng tạo công thức MID, kết hợp của 5 hàm khác nhau.
LEN – để lấy được tổng chiều dài của chuỗi văn bản gốc
REPT – để lặp lại một ký tự cụ thể với số lần nhất định
SUBSTITUTE – thay thế một ký tự bằng một ký tự khác
MID – trích xuất một chuỗi con
TRIM – loại bỏ khoảng không gian dấu cách thêm vào
Ta có công thức chung như sau :
TRIM(MID(SUBSTITUTE(string,” “,REPT(” “,LEN(string))), (N-1)*LEN(string)+1, LEN(string)))
Trong đó:
• String là chuỗi văn bản ban đầu chứa dữ liệu bạn muốn trích xuất.
• N là số thứ tự của từ cần trích xuất
Ví dụ, để trích xuất từ thứ 2 trong chuỗi văn bản ô A2, ta sử dụng công thức sau :
=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))
Hoặc, bạn hoàn toàn có thể nhập số thứ tự từ cần trích xuất ( N ) trong nhiều ô trong cùng công thức, như hình chụp màn hình hiển thị bên dưới :
Cách hoạt động giải trí của công thức trên như sau
Về thực chất, công thức gồm có những từ đơn trong văn bản gốc và các từ cách nhau bởi rất nhiều khoảng trống ( dấu cách ), để tìm được chuỗi tài liệu bạn cần trong cả một khối các “ khoảng chừng trống-từ-khoảng trống ” thì tất cả chúng ta cần phải vô hiệu các khoảng trống đó. Nói đơn cử hơn thì công thức hoạt động giải trí theo logic dưới đây :
• Hàm SUBSTITUTE và REPT sửa chữa thay thế mỗi khoảng chừng trống đơn lẻ trong một chuỗi bằng nhiều khoảng trống. Số lượng khoảng trống được thêm vào bằng tổng chiều dài của chuỗi văn bản gốc được trả về bới hàm LEN : SUBSTITUTE ( A2, ” “, REPT ( ” “, LEN ( A2 ) ) )
Bạn hoàn toàn có thể coi tác dụng mà bạn muốn trích ra từ chuỗi văn bản gốc cũng giống như một “ hành tinh nhỏ ” trôi dạt trong một khoảng trống to lớn gồm : khoảng chừng trống-từ thứ nhất-khoảng trống-từ thứ hai-khoảng trống-từ thứ ba – …. Từ chuỗi văn bản như vậy tất cả chúng ta nhận được đối số của công thức MID .
• Tiếp đến, bạn tính ra vị trí mở màn của chuỗi con mà bạn cần trích xuất ( đối số start_num ) sử dụng phương trình sau : ( N-1 ) * LEN ( A1 ) + 1. Phép tính này trả về ví trí của ký tự tiên phong trong chuỗi con cần trích xuất hoặc vị trí của 1 số ít khoảng trống trong tổng số những khoảng trống trước đó .
• Số ký tự trích xuất ( đối số num_chars ) là phần đơn thuần nhất vì tất cả chúng ta hoàn toàn có thể tìm ra bằng cách lấy tổng chiều dài của chuỗi văn bản gốc : LEN ( A2 ). Qua đó, bạn sẽ vô hiệu được những khoảng trống trong chuỗi dài gồm cả khoảng trống và từ .
• Cuối cùng, hàm TRIM sẽ loại bỏ khoảng trống nằm ở đầu và cuối.
Công thức trên hoạt động tốt trong hầu hết các trường hợp nhưng nếu có từ 2 khoảng trống sát nhau giữa các từ thì kết quả nhận được sẽ bị sau. Để khắc phục lỗi sai này thì nên lồng ghép hàm TRIM vào hàm SUBSTITUTE để loại bỏ các khoảng trống nằm giữa những khoảng trống khác, thực hiện như sau:
=TRIM(MID(SUBSTITUTE(TRIM(A2),” “,REPT(” “,LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))
Các bạn theo dõi hình minh họa dưới đây :
Nếu chuỗi văn bản gốc chứa nhiều khoảng trống giữa các từ và các từ trong đó quá lớn hoặc quá nhỏ thì bạn nên thêm hàm TRIM lồng trong mỗi hàm LEN để tránh xảy ra lỗi :
=TRIM(MID(SUBSTITUTE(TRIM(A2),” “,REPT(” “,LEN(TRIM(A2)))), (B2-1)*LEN(TRIM(A2))+1, LEN(TRIM(A2))))
Công thức này có vẻ như khá phức tạp nhưng lại bảo vệ không xảy ra lỗi .
Cách trích xuất một từ chứa một hoặc nhiều ký tự đơn cử
Ví dụ bên dưới đây cho thấy sự có ích của công thức MID trong Excel để trích xuất một từ chứa một hoặc nhiều ký tự từ văn bản gốc :
TRIM(MID(SUBSTITUTE(string,” “,REPT(” “,99)),MAX(1,FIND(char,SUBSTITUTE(string,” “,REPT(” “,99)))-50),99))
Giả sử văn bản gốc nằm ở ô A2, bạn cần tìm kiếm chuỗi con chứa kí tự “ USD ” ( Ngân sách chi tiêu ) bạn dùng công thức sau :
=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,99)),MAX(1,FIND(“$”,SUBSTITUTE(A2,” “,REPT(” “,99)))-50),99))
Tương tự, bạn hoàn toàn có thể trích xuất địa chỉ email ( dựa trên kí tự @ ) hay tên website ( dựa trên “ www ” ), … .
Cách hoạt động giải trí của công thức trên như sau
Giống như trong ví dụ trước, các hàm SUBSTITUTE và hàm REPT biến mọi khoảng trống trong chuỗi văn bản gốc thành nhiều khoảng trống, đúng chuẩn hơn là 99 khoảng chừng trắng .
Hàm FIND xác lập vị trí của ký tự mong ước ( trong ví dụ này là USD ), tiếp đó bạn trừ đi 50. Làm như vậy bạn sẽ nhận lại được 50 ký tự và đặt ở giữa khối 99 khoảng chừng trống đứng trước chuỗi con chứa ký tự được chỉ định .
Hàm MAX được sử dụng để xử lý tình huống khi chuỗi con mong muốn xuất hiện ở đầu chuỗi văn bản ban đầu. Trong trường hợp này, kết quả của FIND () – 50 sẽ là số âm, và MAX (1, FIND () – 50) sẽ được thay thế bằng 1.
Qua đó, hàm MID sẽ thu thập 99 ký tự tiếp theo và trả về chuỗi con bạn cần. Hàm TRIM giúp bạn loại bỏ hết những khoảng trống ở xung quanh chuỗi con bạn cần đó.
Mẹo: Nếu chuỗi văn bản gốc quá lớn, bạn có thể thay thế 99 và 50 thành con số lớn hơn, ví dụ như 1000 và 500.
Cách khiến hàm MID trả về một số ít
Giống như những hàm văn bản khác, Excel MID luôn trả về chuỗi văn bản, ngay cả khi nó chứa các chữ số trông giống như một con số. Để chuyển đầu ra thành một số, chúng ta chỉ cần thay đổi hàm MID một chút, sử dụng VALUE để chuyển đổi một giá trị văn bản đại diện cho một số.
Ví dụ, để trích xuất 3 ký tự, bắt đầu từ ký tự thứ 7 và chuyển chuỗi văn bản kết quả thành dạng số thì ta sử dụng công thức:
=VALUE(MID(A2,7,3))
Với cách tiếp cận tựa như vận dụng cho những công thức phức tạp hơn. Như trong ví dụ trên, giả sử các mã lỗi ( Erro ) có độ dài khác nhau thì bạn vẫn hoàn toàn có thể trích xuất chúng bằng cách sử dụng công thức MID để nhận được chuỗi ký tự con nằm giữa hai dấu cách, lồng bên trong hàm VALUE :
=VALUE(MID(A2,SEARCH(“:”,A2)+1,SEARCH(“:”,A2,SEARCH(“:”,A2)+1)-SEARCH(“:”,A2)-1))
Trên đây là cách sử dụng hàm MID trong Excel. Cảm ơn các bạn đã theo dõi bài viết .
Chúc các bạn thành công.
Đừng bỏ lỡ bộ tài liệu: Hướng dẫn học Excel cơ bản
Source: https://tbdn.com.vn
Category: Tin Học
