[Kách Hay] Cách sử dụng hàm MID lấy chuỗi ký tự trong Excel mới nhất 2023

Cách sử dụng hàm MID lấy chuỗi ký tự trong Excel – Kách Hay .Com đã tổng hợp thông tin từ nhiều nguồn, giúp bạn có góc nhìn đa chiều hơn. Nào chúng ta bắt đầu thôi

New Page

Xem nhanh

Hàm MID và cách sử dụng hàm MID trong Excel

Thể loại:
Excel đơn giản

Hàm MID trong Excel dùng để cắt một chuỗi ở giữa một chuỗi cho trước. Hiện nay, nó được sử dụng rộng rãi trong tính toán và thống kê bảng tính Excel.

Bài viết của Học Excel Online dưới đây sẽ giúp bạn hiểu rõ hơn về cú pháp và cách sử dụng hàm MID.

xem lướt qua

Cú pháp của hàm MID .
- - ZB
Lưu ý về hàm Excel MID .

Ví dụ sử dụng công thức tính của hàm MID.
- Cách xuất họ và tên
- Sử dụng hàm MID để trích xuất họ
- Sử dụng hàm MID để trích xuất tên
- Cách trích xuất một chuỗi giữa hai khoảng trắng
- Cách trích xuất từ thứ N trong chuỗi văn bản
- Công thức trên hoạt động như sau
- Cách trích xuất một từ chứa một hoặc nhiều ký tự cụ thể
- Công thức trên hoạt động như sau
- Cách lấy hàm MID trả về một số

Cú pháp của hàm MID =MID(text,m,n) – trong đó : + text: string. + m : Vị trí bắt đầu cắt chuỗi. + n : số ký tự cần cắt khỏi chuỗi.

ZB

1 / Lấy 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, bỏ đi 1 ký tự bắt đầu từ vị trí số 4 trong các ô của cột Mã hộ gia đình trong bảng dưới đây. => Kết quả: Những lưu ý quan trọng về hàm MID trong Excel Khi sử dụng hàm MID, bạn không được vội vàng mà phải chú ý một số điều sau để tránh mắc phải những lỗi thường gặp.

Die MID-Funktion gibt immer eine Textzeichenfolge zurück, auch wenn die Textzeichenfolge nur Ziffern enthält. Dies ist erwähnenswert, wenn Sie das Ergebnis einer Berechnung mit der MID-Funktion in anderen Berechnungen verwenden möchten. Um die Ausgabe der MID-Funktion in eine Zahl umzuwandeln, muss die MID-Funktion mit der VALUE-Funktion kombiniert werden.
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 đó.

Các ví dụ sử dụng công thức tính của hàm MID

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,TÌM KIẾM(” “,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ọ.

vi-du-ham-mid-trong-excel-1 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:

vi-du-ham-mid-trong-excel-2 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, TÌM KIẾM(” “,A2)+1, TÌM KIẾM (” “, A2, TÌM KIẾM (” “,A2)+1) – TÌM KIẾM (” “,A2)-1)

Các bạn có thể xem kết quả trong hình ảnh bên dưới:

ta-anh-anh-trung-trong-excel-3

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(chuỗi, TÌM KIẾM(dấu phân cách, chuỗi)+1, TÌM KIẾM (dấu phân cách, chuỗi, TÌM KIẾM (dấu phân cách, chuỗi)+1) – TÌM KIẾM (dấu phân cách, chuỗi)-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:

vi-du-ham-mid-trong-excel-4 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:

trich-xuat-tu-trong-chuoi-van-ban 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:

ta-anh-anh-trung-trong-excel-5

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))

ket-hop-ham-trung-va-giá

Cách tiếp cận tương tự áp dụng cho các công thức phức tạp hơn. Giả sử rằng các mã lỗi (erro) có độ dài khác nhau, 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ư trong ví dụ trên, để lấy một chuỗi con nằm giữa hai khoảng trắng được lồng trong hàm VALUE:

=WERT(MITTE(A2,SUCHE(“:”,A2)+1,SUCHE(“:”,A2,SUCHE(“:”,A2)+1)-SUCHE(“:”,A2)-1))