Hàm MID là một hàm tích hợp trong Excel được phân loại là Hàm chuỗi / văn bản . Chúng có thể được sử dụng như một hàm trang tính (WS) và một hàm VBA (VBA) trong Excel. Trong bài viết dưới đây chúng ta hãy cùng nhau thảo luận “Hàm MID trong Excel – Những phép toán cơ bản và nâng cao” nhé.
Hàm MID trong Excel – cú pháp và những cách sử dụng cơ bản.
Để có thể áp dụng hàm MID thành thạo trong Excel. Bạn cần phải có những kiến thức hiểu biết về hàm này. Đặc biệt là về “cú pháp” và “đặc tính” hàm này trong Excel. Chúng ta cùng điểm qua những thông tin này ngay dưới đây nhé.
Định nghĩa về hàm MID trong Excel.
Hàm MID là một chương trình máy tính mà bạn chạy từ công thức ô trang tính. Chúng có thể bắt đầu từ bất kỳ vị trí ký tự nào trong một đoạn văn bản và di chuyển sang phải để trả về “n” ký tự. Số ký tự trả về dựa trên số ký tự bạn chỉ định. Nói một cách dễ hiểu, Hàm MID trong Excel được sử dụng để trích xuất một phần nhỏ của chuỗi từ chuỗi nhập. Hoặc trả về một số ký tự cần thiết từ văn bản hoặc chuỗi.
Làm cách nào để bạn sử dụng hàm MID trong Excel ?
Bất cứ khi nào bạn nhập công thức vào một ô trang tính, điều này được gọi là cú pháp hoặc ngữ pháp. Cú pháp hàm MID chung có định dạng như thế này khi bạn nhập nó vào một ô trang tính:
= MID (văn bản, start_num, num_chars)
Trong đó Text, Start_Num, Num_Chars ….. được gọi là danh sách đối số của hàm. Đối với cú pháp ở trên, bạn cần bao gồm cả ba đối số cho hàm MID khi nhập chúng vào công thức ô trang tính để nó tính toán chính xác.
Định nghĩa đối số hàm trong trang tính MID
Trong hàm MID thông thường bạn sẽ nhìn thấy có ba đối số. Chính là “Text, Start_Num, Num_Chars”. Những giá trị đối số nào có thể được sử dụng sẽ được thảo luận dưới đây
- Text: Đây là đoạn văn bản có chứa các ký tự bạn muốn trích xuất.
- Start_Num: Chỉ định ký tự bạn muốn bắt đầu trong chuỗi bao gồm khoảng trắng.
- Num_Chars: Chỉ định số ký tự bạn muốn MID trích xuất bắt đầu từ ký tự được chỉ định bởi start_num. Chúng phải lớn hơn hoặc bằng không.
Ví dụ: Bạn cần lấy ra 5 ký tự từ chuỗi văn bản hàng B3, bắt đầu từ ký tự thứ 4, chúng ta sử dụng công thức sau:
=MID(C4;4;1)
=> Kết quả sẽ trả về như sau:
Khi sử dụng hàm MID bạn cần ghi nhớ điều gì?
Khi bạn sử dụng hàm MID trong Excel không nên sử dụng vội vàng. Bạn hãy điểm qua một số điểm cần “ghi nhớ” sau đây. Để tránh mắc phải những lỗi không đáng tiếc này nhé.
*** Công thức MID trong Excel trả về văn bản trống nếu start_num lớn hơn độ dài của chuỗi.
*** Nếu start_num <độ dài của văn bản, nhưng start_num + num_chars vượt quá độ dài của chuỗi thì MID trả về các ký tự đến cuối văn bản.
*** Nếu Start_Num nhỏ hơn 1, MID trả về giá trị lỗi #VALUE! giá trị lỗi.
*** Nếu Num_Chars là số âm, MID trả về giá trị lỗi #VALUE! giá trị lỗi.
Các ví dụ sử dụng công thức tính của hàm MID.
MID trong Excel rất đơn giản và rất dễ sử dụng. Hãy hiểu hoạt động của hàm MID trong Excel bằng một số ví dụ về Công thức MID ngay dưới đây.
Sử dụng hàm MID để trích xuất họ
Giả sử “Tên” và “Họ “đầy đủ nằm trong ô A2. Họ và tên được phân tách bằng ký tự khoảng trắng, bạn có thể lấy “Họ” bằng công thức sau:
=TRIM(MID(A2,SEARCH(” “,A2),LEN(A2)))
Ở đây hàm SEARCH để xác định vị trí bắt đầu (dấu cách).Bạn không cần phải tính toán chính xác vị trí kết thúc. Vì vậy, trong đối số Num_Chars , bạn chỉ cần cung cấp tổng độ dài của chuỗi ban đầu được trả về bởi hàm LEN. Nếu bạn không muốn hàm LEN thì có thể đặt một số thật dài cho chuỗi ký tự cần tìm, ví dụ: 100. Cuối cùng, hàm TRIM loại bỏ khoảng trắng thừa và bạn nhận được kết quả sau:
Sử dụng hàm MID để trích xuất tên
Để trích xuất “tên” từ A2, hãy sử dụng công thức sau:
=MID(A2,1,SEARCH(” “,A2)-1)
Hàm SEARCH được sử dụng để quét chuỗi ban đầu cho ký tự khoảng trắng và trả về vị trí của chúng.
Cách trích xuất chuỗi ký tự nằm giữa hai dấu cách.
Lấy ngay ví dụ ở trên, nếu ngoài họ và tên. Nếu bên cạnh họ và tên, ô A2 còn chứa tên đệm. Làm cách nào để có thể trích xuất được tên đệm đó. Bạn hãy áp dụng công thức MID dưới đây nhé.
Giống như trong ví dụ trước, sử dụng hàm SEARCH để xác định vị trí của dấu cách đầu tiên. Bạn cộng thêm 1 vào đó, để chuỗi ký tự này trích ra bắt đầu bằng ký tự đứng ngay sau dấu cách. Do đó, bạn nhận được đối số Start_Num của công thức MID: SEARCH(” “,A2)+1
Tiếp theo, bạn muốn lấy ký tự của dấu cách thứ 2. Thì bạn hãy áp dụng hàm SEARCH với việc tìm kiếm từ ký tự đầu tiên trong Excel sau dấu cách thứ hai: SEARCH(” “,A2,SEARCH(” “,A2)+1)
Để tìm ra số ký tự cần trả về, hãy trừ vị trí của dấu cách thứ 1 khỏi vị trí của dấu cách thứ 2. Sau đó trừ 1 từ kết quả vì bạn không muốn có thêm dấu cách nào trong chuỗi kết quả. Do đó, bạn có đối số Num_chars : SEARCH (“”, A2, SEARCH (“”, A2) +1) – SEARCH (“”, A2)
Với tất cả các đối số được đặt cùng nhau, đây là công thức Excel Mid để trích xuất chuỗi ký tự đứng giữa 2 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)
Theo cách tương tự, bạn có thể trích xuất một chuỗi con giữa bất kỳ dấu cách nào:
MID ( string , SEARCH ( delimiter , string ) +1, SEARCH ( delimiter , string , SEARCH ( delimiter , string ) +1) – SEARCH ( delimiter , string ) -1)
Ví dụ: để trích được một chuỗi con được phân tách bằng dấu phẩy và dấu cách, hãy sử dụng công thức sau:
=MID(A2,SEARCH(“, “,A2)+1,SEARCH(“, “,A2,SEARCH(“, “,A2)+1)-SEARCH(“, “,A2)-1)
Cách trích xuất từ thứ N trong một chuỗi văn bản
Ví dụ này minh họa cách sử dụng sáng tạo công thức Mid phức tạp trong Excel, bao gồm 5 hàm khác nhau:
LEN – Để lấy tổng chiều dài chuỗi.
REPT – Lặp lại một ký tự cụ thể một số lần nhất định.
SUBSTITUTE – Thay thế một ký tự này bằng một ký tự khác.
MID – Có tác dụng trích xuất một chuỗi con.
TRIM – Loại bỏ khoảng trắng thừa trong chuỗi.
Chúng ta có công thức áp dụng chung sau:
TRIM (MID (SUBSTITUTE ( string , “”, REPT (“”, LEN ( string ))), ( N -1) * LEN ( string ) +1, LEN ( string )))
Ghi chú:
*** String: Là chuỗi văn bản gốc chứa dữ liệu bạn muốn trích xuất.
*** N: Là số thứ tự từ bạn cần trích xuất
Ví dụ: Nếu bạn cần trích xuất từ thứ 2 trong chuỗi văn bản ô A. Chúng ta thực hiện bằ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ố từ cần trích xuất (N) trong nhiều ô trong cùng một công thức, như được hiển thị trong ả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ề cơ bản, công thức gồm nhiều từ đơn trong văn bản gốc. Các từ được cách nhau bởi rất nhiều dấu cách. Để tìm được chuỗi dữ liệu bạn mong muốn trong “khoảng trống-từ-khoảng trống”. Thì trước tiên bạn cần phải loại bỏ được khoảng trống đó. Tóm lại công thức sẽ hoạt động theo Logic sau đây:
- Hàm SUBSTITUTE và REPT thay thế mỗi khoảng trắng đơn lẻ trong chuỗi bằng nhiều khoảng trống. Số lượng khoảng trống bổ sung bằng tổng độ dài của chuỗi ban đầu được trả về bởi LEN: SUBSTITUTE (A2, “”, REPT (“”, LEN (A2)))
- Tiếp theo, bạn tính vị trí bắt đầu của chuỗi con quan tâm ( đối số Start_num ) bằng cách sử dụng công thức sau: (N-1) * LEN (A1) +1. Phép tính này trả về vị trí của ký tự đầu tiên của từ mong muốn hoặc vị trí của một số ký tự khoảng trống trong khoảng cách trước đó.
- Số ký tự cần trích xuất ( đối số Num_chars ) là phần dễ nhất. Bạn chỉ cần lấy độ dài tổng thể của chuỗi ban đầu: LEN (A2). Qua đó, bạn chỉ còn lại chuỗi con từ-khoảng trắng mong muốn .
- Cuối cùng, hàm TRIM loại bỏ các khoảng trống ở đầu và cuối của chuỗi.
Công thức trên bạn có thể áp dụng cho mọi tình huống mà vẫn cho ra kết quả tốt nhất. Tuy nhiên, nếu có 2 hoặc nhiều khoảng trắng liên tiếp giữa các từ , nó sẽ cho kết quả sai. Để khắc phục điều này, hãy lồng một hàm TRIM khác vào bên trong SUBSTITUTE để loại bỏ các khoảng trống thừa ở những khoảng trống khác như sau:
=TRIM(MID(SUBSTITUTE(TRIM(A2),” “,REPT(” “,LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))
Nếu chuỗi gốc của bạn chứa nhiều khoảng trống giữa các từ cũng như các từ rất lớn và rất nhỏ. Hãy nhúng thêm hàm TRIM vào mỗi LEN, chỉ để giúp bạn an toàn:
=TRIM(MID(SUBSTITUTE(TRIM(A2),” “,REPT(” “,LEN(TRIM(A2)))), (B2-1)*LEN(TRIM(A2))+1, LEN(TRIM(A2))))
Công thức này hơi “rườm rà” có vẻ phức tạp. Nhưng chúng tôi đảm bảo rằng đây là công thức cho ra “kết quả” hoàn hảo tất cả các loại chuỗi.
Cách trích xuất một từ chứa một hoặc nhiều ký tự cụ thể
Ví dụ ngay dưới đây cho thấy một công thức Excel Mid không tầm thường. Để trích xuất một từ chứa (các) ký tự cụ thể từ bất kỳ đâu trong chuỗi 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 trong ô A2 và bạn đang tìm kiếm một chuỗi con chứa ký tự “$” (Giá), công thức có dạng sau:
=TRIM(MID(SUBSTITUTE(A2,””,REPT(“”,99)),MAX(1,FIND(“$”,SUBSTITUTE(A2,” “,REPT(” “,99)))-50),99))=
Cách hoạt động của công thức trên như sau
Giống như trong ví dụ trước, hàm SUBSTITUTE và 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 định vị vị trí của ký tự mong muốn.tiếp đó bạn trừ đi 50. Điều này sẽ đưa bạn trở lại 50 ký tự và đặt ở đâu đó ở giữa khối 99 dấu cách đứ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 gốc. Trong trường hợp này, kết quả của FIND () – 50 sẽ là một số âm và MAX (1, FIND () – 50) thay thế nó bằng 1.
Từ điểm bắt đầu đó, hàm MID thu thập 99 ký tự tiếp theo và trả về chuỗi con quan tâm được bao quanh bởi nhiều khoảng trống, như sau: “dấu cách-chuỗi con-dấu cách” . Chức năng hàm TRIM giúp bạn loại bỏ các khoảng trống thừa.
Lưu ý: Nếu chuỗi văn bản gốc được trích xuất rất lớn, hãy thay thế 99 và 50 bằng các số lớn hơn, chẳng hạn như 1000 và 500.
Cách khiến hàm MID trả về một số.
Cũng giống như các hàm văn bản khác, hàm MID trong Excel luôn trả về một chuỗi văn bản. Ngay cả khi chúng chỉ chứa các chữ số và trông giống như một con số. Để có thể thực hiện đầu ra thành một con số. Bạn cần thay đổi hàm MID, thay vào đó là hàm VALUE để chuyển đổi giá trị văn bản đại diện cho một số thành một số.
Ví dụ: Để trích xuất một chuỗi 3 ký tự bắt đầu bằng ký tự thứ 7 và chuyển đổi nó thành một số, hãy sử dụng công thức sau:=
=VALUE(MID(A2,7,3))
Trong công thức này giả sử các mã lỗi có độ dài thay đổi. Bạ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))
Ngoài ra các ví dụ khác về cách sử dụng hàm MID trong Excel:
*** Cách loại bỏ khoảng trống ở đầu – Công thức Mid Trim để chỉ xóa khoảng trống trước các từ, giữ nguyên nhiều khoảng trắng ở giữa.
*** Trích xuất N ký tự theo sau một ký tự nhất định – Công thức Mid trong Excel nâng cao chỉ ra cách trích xuất một chuỗi con có độ dài nhất định sau sự xuất hiện cụ thể của dấu phân cách / ký tự.=
*** Cách trích xuất tên miền từ URL – Một công thức rất thông minh để trích xuất tên miền có hoặc không có www. và với bất kỳ giao thức nào (http, https, ftp, v.v.).
Như vậy bài viết này chúng tôi đã hướng dẫn rất chi tiết cho bạn về Hàm MID trong Excel – Những phép toán cơ bản và nâng cao. Với các ví dụ cụ thể, từ cơ bản đến các công thức phức tạp. Chúng tôi luôn luôn có hình ảnh minh họa để bạn hiểu hơn về hàm MID. Và biết áp dụng chúng trong các trường hợp cụ thể hiệu quả nhất. Nếu bạn thấy bài viết hữu ích, đừng quên chia sẻ cho bạn bè của mình nhé. Chúc các bạn thành công !!
>>> Xem thêm: Định nghĩa hàm LEN trong Excel. – digitalfuture
>>> Xem thêm: Hướng dẫn về cách sử dụng SUMIFS (Hàm SUMIFS) trong Excel – digitalfuture
