Cách thức hoạt động của hàm MID và ứng dụng trong Excel

Hàm MID là công cụ được sử dụng để trích xuất các ký tự ở vị trí giữa trong một chuỗi ký tự cụ thể trong Excel. Nó rất hữu ích trong việc thực hiện các phép tính và bảng kê liệt kê dữ liệu trên bảng tính Excel.

Trong bài viết này, Siêu Marketing sẽ hướng dẫn bạn cách hiểu và áp dụng hàm MID hiệu quả.

Cách sử dụng hàm MID

=MID(text,m,n)

Trong đó::

+ text: Là chuỗi ký tự cần xử lý.

+ m: Là vị trí ký tự mà từ đó ta bắt đầu cắt chuỗi.

+ n: Là số lượng ký tự được cắt từ chuỗi gốc.

Một số ví dụ minh họa

1/ Để lấy 5 ký tự từ chuỗi tại vị trí thứ 4, ta dùng công thức: =MID(B3;4;5).

Hàm mid trong excel

=> Kết quả thu được là:

Cách sử dụng hàm Mid trong excel

2/ Ở cột F, ta cần trích 1 ký tự từ vị trí thứ 4 của chuỗi trong cột Mã hộ từ bảng dữ liệu dưới đây.

Hướng dẫn sử dụng hàm Mid trong excel

=> Và ta nhận được kết quả:

Hướng dẫn cách dùng hàm Mid trong excel

Một số chú ý khi dùng hàm MID trong Excel

Trong quá trình sử dụng hàm MID cần lưu tâm đến những vấn đề sau để tránh nhập sai thông tin:

  • Hàm MID luôn đưa ra kết quả dưới dạng chuỗi văn bản, kể cả khi nội dung chỉ gồm các chữ số. Để sử dụng kết quả hàm MID ở dạng số trong các phép toán khác, bạn cần phải dùng kết hợp với hàm VALUE để chuyển đổi nó.
  • Nếu vị trí bắt đầu (start_num) lớn hơn độ dài của chuỗi gốc, hàm MID sẽ trả về chuỗi rỗng (“”).
  • Nếu start_num nhỏ hơn 1, hàm MID sẽ báo lỗi #VALUE.
  • Nếu num_chars có giá trị âm, hàm MID sẽ trả về lỗi #VALUE!; trường hợp num_chars bằng 0 sẽ trả về một chuỗi không chứa ký tự nào (ô rỗng).
  • Nếu tổng của start_num và num_chars vượt qua độ dài chuỗi gốc, hàm MID sẽ cắt chuỗi bắt đầu từ start_num đến ký tự cuối cùng của chuỗi.

Một số ví dụ áp dụng công thức của hàm MID

Bạn thường cần phối hợp hàm MID với các hàm khác khi áp dụng nó trong Excel, dưới đây là một số ví dụ:

Cách trích xuất họ và tên sử dụng hàm MID

Tiến hành trích xuất họ và tên riêng biệt bằng cách kết hợp hàm LEFT và hàm RIGHT. Tuy nhiên vẫn có phương pháp khác để thực hiện việc này.

Áp dụng hàm MID để lấy họ ra từ tên đầy đủ

Nếu tên đầy đủ nằm trong ô A2 và họ và tên cách nhau bằng khoảng trống, bạn có thể nhận được họ bằng công thức sau đây:

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

SEARCH được dùng để tìm vị trí của khoảng trắng, giúp loại bỏ nó. Tiếp đó, MID được sử dụng để lấy ra phần chuỗi từ ký tự đầu tiên đến ký tự trước khoảng trống, qua đó thu được họ từ tên đầy đủ.

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

Ứng dụng hàm MID để rút tên ra

Công thức dưới đây sẽ giúp bạn lấy tên từ dữ liệu ở ô A2:

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

Hàm SEARCH ở đây được dùng để xác định điểm khởi đầu của chuỗi, từ dấu cách. Tính đến vị trí cuối cùng không cần thiết vì nếu tổng của start_num và num_chars vượt quá độ dài của chuỗi ban đầu, hàm sẽ tự động trả về tất cả ký tự còn lại. Vậy nên ta sẽ dùng hàm LEN để lấy đối số num_chars là chiều dài tổng của chuỗi. Nếu chưa áp dụng hàm LEN, một con số đủ lớn như 100 cũng có thể sử dụng để chỉ định số ký tự lấy ra. Hàm TRIM cuối cùng sẽ xóa bỏ các khoảng trắng thừa và đây là kết quả thu được:

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

Phương pháp để lấy chuỗi nằm giữa hai dấu cách

Giả sử bạn cần lấy tên giữa nếu dữ liệu có chứa cả tên lót, bạn cần làm sao?
Bạn có thể áp dụng cách sau:

Cũng tương tự như trên, hàm SEARCH vẫn là công cụ để xác định vị trí dấu cách đầu tiên, với việc cộng thêm 1 để chuỗi bắt đầu từ sau dấu cách. Điều này cho ta tham số start_num cho hàm MID là: SEARCH(” “, A2)+1

Tiếp tục, để tìm khoảng trắng thứ hai chúng ta dùng lại hàm SEARCH với điểm bắt đầu sau dấu cách đầu tiên: SEARCH(” “, A2, SEARCH(” “, A2) + 1)

Và chúng ta sẽ lấy đối số num_chars bằng cách lấy vị trí của dấu cách thứ hai trừ đi vị trí dấu cách đầu tiên: SEARCH (” “, A2, SEARCH (” “,A2)+1) – SEARCH (” “, A2)
Kết hợp các phần trên lại để tạo công thức MID trích xuất chuỗi ký tự giữa hai khoảng trống do hai dấu cách tạo nên:

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

Kết quả sẽ được thể hiện qua hình ảnh dưới đây:

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

Bằng cách tương tự, bạn cũng có thể rút một phần ngắn bất kỳ nằm giữa hai dấu cách ví dụ:

MID(chuỗi, SEARCH(dấu phân cách, chuỗi)+1, SEARCH (dấu phân cách, chuỗi, SEARCH (dấu phân cách, chuỗi)+1) – SEARCH (dấu phân cách, chuỗi)-1)

Chẳng hạn, bạn muốn trích một đoạn được phân cách bởi dấu phẩy và dấu cách, công thức sẽ như sau:

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

Và hãy xem hiệu quả của công thức thông qua hình dưới đây:

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

Phương pháp để rút ra từ thứ N từ chuỗi văn bản

Dưới đây là một bài toán mẫu cho việc áp dụng công thức MID một cách phối hợp và sáng tạo, với sự kết hợp từ 5 hàm khác nhau.
LEN – giúp định lượng chiều dài tổng của chuỗi văn bản gốc
REPT – dùng để tái tạo ký tự nào đó theo số lượng cần thiết
SUBSTITUTE – sẽ thay ký tự này bằng ký tự khác
MID – dùng để lấy một chuỗi con nhất định
TRIM – dùng để loại bỏ các khoảng trống thừa

Công thức tổng quát như sau:

TRIM(MID(SUBSTITUTE(string,” “,REPT(” “,LEN(string))), (N-1)*LEN(string)+1, LEN(string)))
Ở đây:
String biểu thị cho chuỗi văn bản mà bạn muốn trích xuất từ nó.
N chỉ số tuần tự của từ mà bạn muốn rút ra

Chẳng hạn, công thức để trích xuất từ thứ hai trong chuỗi tại ô A2 sẽ là:

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

Ngoài ra, số thứ tự của từ mà bạn muốn trích (N) có thể nhập vào nhiều ô khác nhau trong cùng một công thức, như hình minh họa dưới đây:

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

Bí quyết hoạt động của công thức đã trình bày

Căn bản, công thức tạo ra những từ riêng biệt trong một chuỗi và mỗi từ được phân cách bởi rất nhiều khoảng trống, nhằm trích tìm ra dãy dữ liệu cần thiết từ trong khối “khoảng trống-từ-khoảng trống”. Cụ thể, công thức này áp dụng logic như sau:

• Sự kết hợp của SUBSTITUTE và REPT thực hiện công việc thay thế mỗi khoảng trống bằng một lượng khoảng trống lớn hơn, số lượng này bằng với chiều dài tổng cộng của chuỗi văn bản gốc theo hàm LEN: SUBSTITUTE(A2,” “,REPT(” “,LEN(A2)))

Bạn có thể hình dung rằng kết quả bạn cần lấy ra từ chuỗi ban đầu giống như “hành tinh nhỏ” trong không gian bao quanh bởi hàng loạt các khoảng trống và từ. Từ chuỗi đã được chế tạo ra này, chúng ta có tham số cần cho công thức MID.

• Sau đó, bạn xác định vị trí bắt đầu của phần chuỗi cần lấy ra (đối số start_num) bằng cách sử dụng công thức: (N-1) * LEN (A1) +1. Việc tính này sẽ cho bạn biết vị trí ký tự đầu tiên của từ cần lấy hoặc số khoảng trống nằm trước từ đó.

• Đối số num_chars, hay số lượng ký tự cần trích xuất, là phần đơn giản bởi vì có thể xác định bằng cách lấy chiều dài chuỗi văn bản gốc: LEN(A2). Điều này giúp loại bỏ những khoảng trống thừa trong chuỗi dài kết hợp của từ và khoảng trống.

• Cuối cùng, hàm TRIM sẽ loại bỏ khoảng trống ở đầu và cuối chuỗi.
Nói chung, công thức này hoạt động hiệu quả trong hầu hết tình huống, tuy nhiên nếu 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. Để xử lý sự cố này thì nên kết hợp hàm TRIM trong công thức.

Sử dụng hàm SUBSTITUTE để loại trừ khoảng trắng thừa

Để loại bỏ các khoảng trắng không cần thiết ở giữa các từ, bạn thực hiện theo cách dưới đây:

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

Xem hình minh họa phía dưới để hiểu rõ hơn:

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

Đối với trường hợp chuỗi ban đầu chứa nhiều khoảng cách và các từ cách nhau quá rộng hoặc quá sát, cần áp dụng hàm TRIM trong mỗi LEN để hạn chế 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 tuy phức tạp nhưng đều an toàn trong việc tránh sai sót.

Phương pháp trích xuất từ chứa một hoặc nhiều ký tự đặc biệt

Qua ví dụ sau ta có thể nhận thấy sự tiện ích của công thức MID trong việc lấy ra từ chứa ký tự đặc biệt trong chuỗi văn bản:

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

Nếu cần tìm chuỗi con chứa ký tự “$” (biểu thị giá tiền) từ văn bản ở ô A2, sử dụng phép tính sau:

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

Tương tự như thế, có thể trích ra địa chỉ email (có ký tự @) hoặc tên trang web (có “www”), v.v.

Mô tả cách thức công thức trên hoạt động

Chức năng của SUBSTITUTE kết hợp với REPT làm các khoảng trống biến thành chuỗi gồm 99 khoảng trống, giống như ví dụ trước đó.

FIND tìm vị trí của ký tự cần tìm (ví dụ ký tự $), sau đó trừ đi 50, giúp ta thu được một chuỗi 50 ký tự, bao quanh bởi 99 khoảng trống đặt trước đó.

MAX chịu trách nhiệm xử lý khi ký tự cần tìm nằm ngay đầu chuỗi. Khi đó, FIND() – 50 sẽ là số âm và MAX(1, FIND() – 50) sẽ chọn giá trị 1.
Mục đích là để hàm MID lấy 99 ký tự kế tiếp và thu về chuỗi cần tìm. TRIM loại bỏ các khoảng trống xung quanh chuỗi đó.

Mẹo: Nếu chuỗi ban đầu quá dài, ta có thể thay thế 99 và 50 bằng số lớn hơn như 1000 và 500.

Cách hàm MID có thể trả về số

MID giống như các hàm văn bản khác trong Excel, luôn trả về chuỗi ký tự, cho dù nó chứa số. Để chuyển đổi kết quả ra dạng số, chỉ cần điều chỉnh hàm MID bằng cách sử dụng VALUE.
Ví dụ, muốn lấy 3 ký tự từ vị trí thứ 7 và chuyển chúng thành số, ta dùng công thức:

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

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

Với cách làm này, bạn cũng có thể áp dụng cho các công thức phức tạp hơn. Giả sử các mã lỗi (Erro) có chiều dài khác nhau, công thức MID kết hợp với VALUE vẫn có thể lấy ra chính xác chuỗi con mong muốn:

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

Xin cảm ơn quý độc giả đã theo dõi bài viết về cách sử dụng hàm MID trong Excel.

Chúc mọi người áp dụng thành công.

Trương Thành Tài

    [submission_id id-lien-he]

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