Hàm MID trong Excel được dùng để làm 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ổ quát trong quá trình tính toán và thống kê trên bảng tính Excel.

Bạn Đang Xem: Hàm MID và cách sử dụng hàm MID trong Excel

Nội dung bài viết tại chỗ này, Học Excel Trực Tuyến sẽ khiến cho bạn làm 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 số đó:

+ text: Chuỗi ký tự.

+ m: Vị trí khai mạc 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 khởi đầ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ự, khởi đầu từ vị trí số 4 trong các ô thuộc cột Mã hộ trong bảng số liệu tại chỗ nà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 phạm phải những lỗi sai phổ quát.

• Hàm MID luôn trả về một chuỗi văn bản, ngay cả những lúc 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 như 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ì nên cần phối hợp hàm MID với hàm VALUE.
• Nếu start_num to 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ự khởi đầu từ start_num cho tới 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 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ọ

Tất cả chúng ta có thể trích xuất họ, tên tuần tự bằng phương pháp sử dụng hàm LEFT và hàm RIGHT. Tuy nhiên, tất cả chúng ta cũng tồn tại 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ũng có thể trích xuất họ bằng phương pháp sử dụng công thức sau:

=MID(A2,1,SEARCH(” “,A2)-1)

Chức năng SEARCH được dùng để làm bỏ dấu cách và trả lại vị trí của nó, vì thế bạn cũng có thể bỏ qua dấu cách đó. Sau đó, bạn dùng hàm MID để trích ra chuỗi con khởi đầu từ ký tự trước hết cho tới 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:

Xem Thêm : Keep Going Là Gì

=TRIM(MID(A2,SEARCH(” “,A2),LEN(A2)))

Lần này, SEARCH được sử dụng để xác định vị trí khai mạc (từ chỗ dấu cách). Không cần tính chuẩn xác vị trí kết thúc (vì nếu start_num và num_chars cộng lại to hơn tổng chiều dài chuỗi thì hàm sẽ trả về tất cả những ký tự còn sót lại trong chuỗi văn bản gốc). Vì vậy, trong đối số num_chars, bạn chỉ việc cung cấp tổng chiều dài của chuỗi lúc đầu được trả về bằng phương pháp sử dụng hàm chức năng LEN. Nếu không dùng hàm LEN thì bạn cũng 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 trong lòng hai dấu cách

Như ví dụ ở phía 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ũng có thể thực hiện theo phương pháp sau đây:

Giống như ở ví dụ trước, tất cả chúng ta vẫn sử dụng hàm SEARCH để xác định vị trí của dấu cách trước hết, thêm vào đó 1 vào đó để chuỗi ký tự trích ra khai mạc bằng ký tự đứng ngay sau dấu cách. Từ đó, các bạn sẽ đã sở hữu thông số start_num từ công thức MID: SEARCH(” “,A2)+1

Tiếp theo, để đã sở hữu vị trí của khoảng tầm cách thứ hai thì tất cả chúng ta sử dụng hàm SEARCH với việc khai mạc tìm kiếm từ ký tự trước hết sau dấu cách thứ hai: SEARCH(” “,A2,SEARCH(” “,A2)+1)

Để đã sở hữu chuỗi ký tự trả về, cần trừ đi vị trí 2 không gian của 2 dấu cách. Từ đó, tất cả chúng ta có đối số num_chars: SEARCH (” “, A2, SEARCH (” “,A2)+1) – SEARCH (” “,A2) Phối hợp lại với nhau tất cả chúng ta đã sở hữu công thức MID để trích ra chuỗi ký tự đứng giữa hai không gian 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ũng có thể xem kết quả trong hình ảnh phía dưới:

Tương tự, bạn cũng có thể trích xuất một chuỗi con nằm trong lòng 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 tầm 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 phía 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ụ tiêu biểu cho việc phối hợp sáng tạo công thức MID, phối 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 – để tái diễn 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ỏ không gian 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 số đó: • String là chuỗi văn bản lúc đầu chứa tài liệu bạn muốn trích xuất. • N là số trật tự của từ cần trích xuất

Ví dụ, để trích xuất từ thứ hai 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ũng có thể nhập số trật 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ị phía dưới:

Cách hoạt động 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 tầm trống (dấu cách), để tìm được chuỗi tài liệu bạn cần phải trong cả một khối các “khoảng tầm trống-từ-khoảng trống” thì tất cả chúng ta cần phải loại bỏ các khoảng tầm trống đó. Nói cụ thể hơn thì công thức hoạt động theo logic tại chỗ này:

• Hàm SUBSTITUTE và REPT thay thế mỗi khoảng tầm trống đơn lẻ trong một chuỗi bằng nhiều khoảng tầm trống. Số lượng khoảng tầm 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ũng 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 tầm 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í khai mạc của chuỗi con mà bạn cần phải 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ự trước hết trong chuỗi con cần trích xuất hoặc vị trí của một số khoảng tầm trống trong tổng số những khoảng tầm trống trước đó.

Xem Thêm : Thế nào là xe Hatchback, SUV, Sedan, Pick-up,Van

• Số ký tự trích xuất (đối số num_chars) là phần đơn giản nhất vì tất cả chúng ta có thể tìm ra bằng phương pháp lấy tổng chiều dài của chuỗi văn bản gốc: LEN(A2). Thông qua đó, các bạn sẽ loại bỏ được những khoảng tầm trống trong chuỗi dài gồm cả khoảng tầm trống và từ.

• Cuối cùng, hàm TRIM sẽ loại bỏ khoảng tầm trống nằm ở đầu và cuối. Công thức trên hoạt động tốt trong hồ hết các trường hợp nhưng nếu có từ 2 khoảng tầm 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 tầm trống nằm trong lòng những khoảng tầm 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 tại chỗ này:

Nếu chuỗi văn bản gốc chứa nhiều khoảng tầm trống giữa các từ và các từ trong đó quá to 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òn 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ụ phía 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 phải 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ũng 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 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 tầm trống trong chuỗi văn bản gốc thành nhiều khoảng tầm trống, chuẩn xác hơn là 99 khoảng tầm 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 các bạn sẽ nhận lại được 50 ký tự và để tại giữa khối 99 khoảng tầm 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 lúc đầ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ẽ tiến hành thay thế bằng 1. Thông qua đó, hàm MID sẽ thu thập 99 ký tự tiếp theo và trả về chuỗi con bạn cần phải. Hàm TRIM khiến cho bạn loại bỏ hết những khoảng tầm trống ở xung quanh chuỗi con bạn cần phải đó.

Mẹo: Nếu chuỗi văn bản gốc quá to, bạn cũng có thể thay thế 99 và 50 thành số lượng to 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ả những lúc nó chứa các chữ số trông giống như một số lượng. Để chuyển đầu ra thành một số, tất cả chúng ta chỉ việc thay đổi hàm MID một tẹo, sử dụng VALUE để chuyển đổi một giá trị văn bản thay mặt đại diện cho một số. Ví dụ, để trích xuất 3 ký tự, khởi đầ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ự ứng 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 phương pháp sử dụng công thức MID để nhận được chuỗi ký tự con nằm trong lòng 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 nội dung 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