Hàm MID và cách sử dụng trong Excel từ cơ bản tới nâng cao

Trong Excel, hàm MID là một công cụ dùng để tương tác với chuỗi ký tự, cho phép người dùng trích lấy thông tin ở vị trí trung tâm của một chuỗi. Bài viết dưới đây sẽ đề cập tới cấu trúc cú pháp và đặc điểm của hàm MID, tiếp theo đó Siêu Marketing sẽ chỉ dẫn một số phương pháp thực tiễn để áp dụng hàm này.

Cách dùng hàm MID trong Excel – cú pháp và ứng dụng cơ bản

Hàm MID trong Excel chủ yếu giúp người dùng rút trích một phân đoạn từ chuỗi văn bản ban đầu. Cụ thể, chức năng của nó là trả về đoạn văn bản xác định từ vị trí cụ thể mà bạn đã lựa chọn.

Đối số của hàm MID trong Excel bao gồm:

MID(text, start_num, num_chars)

Trong đó,
Text đại diện cho chuỗi văn bản nguồn
Start_num là vị trí bắt đầu của thông tin cần trích
Num_chars là số lượng ký tự cần lấy ra

Ví dụ: Nếu muốn trích 8 ký tự từ dòng văn bản trong ô A2, khởi đầu từ ký tự thứ mười, bạn sẽ dùng công thức:

=MID(A2,10, 8)

Kết quả được hiển thị như hình minh họa sau:

trich-du-lieu-su-dung-ham-mid-trong-excel
trich-du-lieu-su-dung-ham-mid-trong-excel

5 điểm chính cần nhớ khi dùng hàm MID trên Excel

Khi dùng hàm MID, bạn cần lưu ý một số điểm quan trọng để tránh sai lầm thường gặp.

  1. Hàm MID luôn luôn xuất ra dạng chuỗi ký tự, kể cả khi nó chỉ chứa số. Vì thế, nếu bạn muốn biến kết quả từ hàm MID thành một con số để tính toán, bạn cần sử dụng hàm VALUE kết hợp cùng hàm MID.
  2. Khi start_num vượt quá chiều dài chuỗi văn bản mẹ, công thức MID sẽ cho ra một chuỗi trống (“”).
  3. Nếu start_num nhỏ hơn giá trị 1, hàm MID sẽ phát sinh lỗi #VALUE.
  4. Khi num_chars nhận giá trị âm, công thức của hàm MID sẽ báo lỗi #VALUE!. Khi num_chars bằng 0, nó sẽ cho ra một chuỗi không chứa ký tự nào (tức là rỗng).
  5. Lúc tổng số của start_num cùng num_chars lớn hơn chiều dài chuỗi gốc thì MID sẽ cung cấp chuỗi từ start_num đến ký tự cuối cùng của chuỗi gốc.

Một số ví dụ minh họa cách sử dụng hàm MID

Hàm MID thường được kết hợp cùng các hàm khác trong Excel như từ những dẫn dụ sau đây sẽ chỉ ra.

Cách tách tên và họ ra từ hàm MID

Có khả năng tách biệt họ và tên sử dụng hàm LEFT và RIGHT. Song, chúng ta cũng có thể dùng cách khác để làm điều này.

Dùng hàm MID để lấy họ từ họ tên

Ví dụ, nếu thông tin họ tên đầy đủ được đặt trong ô A2 và họ tên tách biệt nhau bằng dấu cách, bạn có thể sử dụng công thức bên dưới để tách họ ra:

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

Công dụng của hàm SEARCH ở đây là xác định vị trí của dấu cách và loại bỏ nó. Dựa trên vị trí tìm được, hàm MID sẽ giúp bạn thu về phần văn bản từ ký tự đầu tiên tới ký tự nằm ngay trước dấu cách, tức là lấy được phần họ.

vi-du-ham-mid-trong-excel-1
vi-du-ham-mid-trong-excel-1

Cách áp dụng hàm MID để lấy tên ra

Bạn có thể lấy tên từ ô A2 bằng cách áp dụng công thức dưới đây:

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

Hàm SEARCH lúc này đóng vai trò xác định nơi đặt ký tự đầu tiên (sau khoảng trắng). Không quan trọng phải tính nơi kết thúc chuỗi chính xác, bởi lẽ nếu như giá trị của start_num và num_chars cùng nhau vượt quá độ dài của chuỗi ban đầu, hàm sẽ trả về tất cả ký tự còn lại. Do đó ở đối số num_chars, bạn cứ sử dụng độ dài của chuỗi được xác định bởi hàm LEN. Còn trong trường hợp không dùng LEN thì bạn có thể nhập vào một con số rất lớn, như 100 chẳng hạn, cho số ký tự muốn lấy. Cuối cùng, hàm TRIM sẽ loại bỏ các khoảng trống dư thừa, cho ta kết quả như dưới đây:

vi-du-ham-mid-trong-excel-2
vi-du-ham-mid-trong-excel-2

Bí quyết để lấy ra đoạn chuỗi giữa 2 khoảng trắng

Nếu trong tình huống có tên đệm trong họ tên ở ví dụ phía trên, bạn sẽ phải làm sao để lấy được tên đệm ra?
Phương pháp tiếp cận được mô tả sau:

Giống như ở ví dụ trước, ta tiếp tục dùng hàm SEARCH để tìm dấu cách đầu và cộng thêm 1, khiến chuỗi giá trị bắt đầu ngay sau khoảng trắng ấy. Kết quả cho ta tham số start_num của MID: SEARCH(” “,A2)+1

Cần tìm khoảng trắng thứ hai, ta lại dùng hàm SEARCH, lần này bắt đầu tìm kiếm từ vị trí ngay sau khoảng trắng đầu tiên: SEARCH(” “,A2,SEARCH(” “,A2)+1)

Để chuỗi trả về đúng, chúng ta sẽ trừ vị trí hai dấu cách từ num_chars. Ta có đối số num_chars như sau: SEARCH (” “, A2, SEARCH (” “,A2)+1) – SEARCH (” “,A2)
Khi ghép chúng lại, ta được công thức MID để lấy chuỗi nằm giữa hai dấu cách như sau:

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

Kết quả của việc áp dụng công thức này có thể thấy rõ ở hình ảnh dưới đây:

vi-du-ham-mid-trong-excel-3
vi-du-ham-mid-trong-excel-3

Để lấy ra một chuỗi con nằm giữa hai dấu cách bất kỳ, bạn có thể làm tương tự như sau:

MID(string, SEARCH(delimiter, string)+1, SEARCH (delimiter, string, SEARCH (delimiter, string)+1) – SEARCH (delimiter, string)-1)

Chẳng hạn, bạn muốn tách chuỗi con có dấu phẩy và khoảng cách làm dấu phân cách, bạn dùng công thức này:

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

Hình dưới minh họa hiệu quả của công thức này:

vi-du-ham-mid-trong-excel-4
vi-du-ham-mid-trong-excel-4

Cách lấy ra từ thứ N từ một chuỗi ký tự

Đây là một minh họa ví dụ về cách kết hợp công thức MID một cách phức tạp hơn, thông qua sự hài hòa của 5 hàm sau:
LEN – để tính tổng số ký tự có trong chuỗi ký tự gốc
REPT – để tạo ra nhiều lần lặp của một ký tự cho trước
SUBSTITUTE – để thực hiện việc thay thế ký tự này bằng ký tự khác
MID – để lấy ra một phân đoạn con của chuỗi
TRIM – để loại bỏ các dấu cách không cần thiết

Dưới đây là công thức tổng quát được đề xuất:

TRIM(MID(SUBSTITUTE(string,” “,REPT(” “,LEN(string))), (N-1)*LEN(string)+1, LEN(string)))
Ở đó:
String chính là chuỗi văn bản nguồn chứa thông tin muốn lấy ra.
N đại diện cho thứ tự của từ bạn muốn rút trích

Nếu muốn tách từ thứ hai trong chuỗi văn bản có trong ô A2, công thức áp dụng sẽ là:

=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))

Bạn cũng có thể điền số N ngay vào công thức cho nhiều tếp bên cạnh nhau, như hình minh họa sau cho thấy:

trich-xuat-tu-trong-chuoi-van-ban
trich-xuat-tu-trong-chuoi-van-ban

Giải thích cách thực hiện của công thức nói trên

Bản chất của công thức này là tìm từ riêng biệt trong văn bản nguyên bản, nơi mà các từ tách biệt bởi rất nhiều dấu cách. Để chỉ ra dãy dữ liệu bạn cần từ một cụm của “khoảng trống-từ-khoảng trống”, chúng ta cần phải bỏ đi những dấu cách này. Chi tiết cụ thể thì công thức chạy theo lô-gic sau:

• Hàm SUBSTITUTE và REPT sẽ thay thế mọi dấu cách đơn giản trong chuỗi bằng một chuỗi các dấu cách. Số dấu cách được thêm vào sẽ bằng chiều dài chuỗi văn bản nguyên thủy, được hàm LEN định lượng: SUBSTITUTE(A2,” “,REPT(” “,LEN(A2)))

Thứ bạn muốn lấy từ văn bản nguyên thủy có thể được tưởng tượng như một “hành tinh bé nhỏ” lênh đênh trong vũ trụ rộng lớn gồm: khoảng trống-từ đầu tiên-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 có được thông số cho công thức MID.

• Bạn tiếp tục xác định vị trí khởi đầu của chuỗi con cần tách (tham số start_num) qua công thức như sau: (N-1) * LEN (A1) +1. Công thức này sẽ trả về vị trí của ký tự đầu tiên bạn cần tách hoặc của một số dấu cách trước nó.

• Số ký tự được lấy ra (tham số num_chars) chính là bước dễ dàng nhất, bởi chúng ta sẽ tiến hành lấy chiều dài toàn bộ chuỗi văn bản nguyên bản: LEN(A2). Nhờ đó, ta có thể loại bỏ được các dấu cách thừa trong chuỗi dài bao gồm từ và khoảng trống.

• Cuối cùng, hàm TRIM sẽ giúp loại bỏ những khoảng trống không cần thiết ở đầu và cuối chuỗi đã tách.
Dù công thức trên thường xử lý tốt đa phần trường hợp nhưng trong tình huống có hai khoảng trống liền kề giữa các từ thì kết quả sẽ không chính xác. Để sửa lỗi này, việc kết hợp hàm TRIM vào hàm SUBSTITUTE là cần thiết.

Để xử lý việc xoá các khoảng trắng thừa ở giữa các từ, bạn có thể áp dụng cách làm như sau:

=TRIM(MID(SUBSTITUTE(TRIM(A2), ” “, REPT(” “, LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))

Xem hình mẫu minh họa dưới đây để hiểu rõ hơn:

vi-du-ham-mid-trong-excel-5
vi-du-ham-mid-trong-excel-5

Khi bản văn gốc có các từ bị cách xa nhau quá nhiều hoặc quá gần, bạn cần nhúng hàm TRIM vào trong mỗi hàm LEN để tránh các lỗi không đáng có:

=TRIM(MID(SUBSTITUTE(TRIM(A2), ” “, REPT(” “, LEN(TRIM(A2)))), (B2-1)*LEN(TRIM(A2))+1, LEN(TRIM(A2))))

Phương pháp trên có thể hơi rắc rối nhưng lại giúp bảo đảm tính chính xác cao.

Phương pháp trích lấy một từ bao gồm một hoặc nhiều kí tự đặc biệt

Dưới đây là một thí dụ thể hiện tính ứng dụng của hàm MID trong việc trích xuất từ Excel mà trong đó có chứa kí tự đặc biệt:

TRIM(MID(SUBSTITUTE(string, ” “, REPT(” “, 99)), MAX(1, FIND(char, SUBSTITUTE(string, ” “, REPT(” “, 99)))-50), 99))

Lấy một ví dụ, nếu bạn muốn tìm từ có chứa ký tự “$” trong ô A2, công thức sẽ là:

=TRIM(MID(SUBSTITUTE(A2, ” “, REPT(” “, 99)), MAX(1, FIND(“$”, SUBSTITUTE(A2, ” “, REPT(” “, 99)))-50), 99))

Bạn cũng có thể áp dụng để trích xuất các địa chỉ email (dùng kí tự @ nhận biết) hoặc tên trang web (dựa vào “www”).

Mô tả về cơ chế hoạt động của công thức

Chức năng của các hàm SUBSTITUTE và REPT là biến đổi các khoảng trắng trong chuỗi văn bản thành chuỗi mới với 99 dấu cách.

Hàm FIND giúp xác định vị trí của kí tự cần tìm (ví dụ là $) và từ đó trừ đi 50. Điều này giúp có được dải 50 ký tự nằm giữa 99 khoảng trắng trước chuỗi ký tự quan trọng.

Để xử lý trường hợp chuỗi cần tìm ở đầu chuỗi văn bản, hàm MAX sẽ được dùng để chọn số lớn hơn giữa 1 và kết quả FIND () – 50. Như vậy, hàm MID sẽ cắt lấy 99 ký tự tiếp theo để trả lại chuỗi cần tìm, và hàm TRIM sẽ giúp loại bỏ dư thừa khoảng trắng xung quanh.
Lưu ý: Bạn có thể thay đổi số 99 và 50 bằng những con số lớn hơn nếu chuỗi văn bản gốc quá dài, như 1000 và 500 chẳng hạn.

Cách làm cho hàm MID trả về giá trị số

Cũng giống các hàm khác liên quan đến xử lý văn bản, Excel MID luôn cho ra chuỗi kết quả dạng văn bản, kể cả chuỗi đó gồm các số. Để chuyển chuỗi thành số, bạn chỉ cắt MID lại 1 chút và dùng VALUE để chuyển chuỗi văn bản thành số.
Ví dụ muốn trích 3 ký tự từ ký tự thứ 7 của chuỗi văn bản và chuyển nó thành số thì dùng công thức:

=VALUE(MID(A2,7,3))

ket-hop-ham-mid-va-value
ket-hop-ham-mid-va-value

Ứng dụng cùng lối tiếp cận này cho những công thức khó khăn hơn. Như trong thí dụ kể trên, giả định rằng các mã lỗi (Erro) không đồng đều về chiều dài thì bạn vẫn có khả năng trích lấy

Bài viết này hướng dẫn cách thức lấy ra chuỗi con nằm giữa hai ký tự cách nhau dựa trên công thức MID trong Excel và phối hợp với hàm VALUE:

=VALUE(MID(A2,SEARCH(“:”,A2)+1,SEARCH(“:”,A2,SEARCH(“:”,A2)+1)-SEARCH(“:”,A2)-1))

Bài viết kết thúc ở đây với hướng dẫn sử dụng hàm MID. Xin cảm ơn quý đọc giả đã quan tâm theo dõi.

Để ứng dụng Excel một cách hiệu quả trong công việc, chúng ta cần phải hiểu sâu về các hàm và biết cách sử dụng các công cụ của Excel. Có những hàm cao cấp như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… và những công cụ không thể thiếu như Data validation, Conditional formatting, và Pivot table…

Bạn có thể học được tất cả những kiến thức này qua khóa học SM90 – Excel từ cơ bản đến chuyên gia của Siêu Marketing. Hiện tại, có những ưu đãi đặc biệt dành cho những bạn đăng ký tham gia khóa học. Để biết thêm chi tiết, vui lòng truy cập: sieumarketing.com

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop