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 đó:
+ 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 thể vội vàng và cần phải ghi nhớ một số điều để tránh việc mắc phải những lỗi sai phổ biến.
- 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 đó.
Khi sử dụng hàm MID trong Excel bạn thường sẽ phải kết 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 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, chúng ta cũng có thể thực hiện được bằng phương pháp khác.
Sử dụng hàm MID để trích xuất họ
Giả sử, họ và tên đầy đủ nằm trong ô A2, họ và tên được phân cách với nhau bằng dấu cách, bạ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ì thế bạn có thể bỏ qua dấu cách đó. Sau đó, bạn dùng hàm MID để trích ra chuỗi con bắt đầu từ ký tự đầu tiên 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 định vị trí bắt đầu (từ chỗ dấu cách). Không cần tính chính xá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ề tất cả 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 cấp tổng chiều dài của chuỗi ban đầu được trả về bằng cách sử dụng hàm chức năng LEN. Nếu không dùng hàm LEN thì bạn có thể đặt một số 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ẽ loại bỏ phần dấu cách và cho ra kết 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 có thể xem kết quả trong hình ảnh bên dưới:
Tương tự, bạ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 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 bên dưới:
Cách hoạt động của công thức trên như sau
Về bản chất, công thức bao gồm 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 dữ liệu bạn cần trong cả một khối các “khoảng trống-từ-khoảng trống” thì chúng ta cần phải loại bỏ các khoảng trống đó. Nói cụ thể hơn thì công thức hoạt động theo logic dưới đây:
• Hàm SUBSTITUTE và REPT thay thế mỗi khoả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 có thể coi kết quả 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 không gian rộng lớn gồm: khoả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 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í bắt đầu 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ự đầu tiên trong chuỗi con cần trích xuất hoặc vị trí của một số 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 giản nhất vì chúng ta 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ẽ loại bỏ đượ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ẻ khá phức tạp nhưng lại đảm bảo 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ự cụ thể
Ví dụ bên dưới đây cho thấy sự hữu í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ự “$” (giá cả) 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 có thể trích xuất địa chỉ email (dựa trên kí tự @) hay tên trang web (dựa trên “www”), ….
Cách hoạt động 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, chính xác hơn là 99 khoảng trắng.
Hàm FIND xác định vị trí của ký tự mong muốn (trong ví dụ này là $), 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 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ố
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ương tự áp 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 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
