Cách dùng hàm mid trong Excel để lọc dữ liệu, cắt chuỗi
Cách dùng hàm mid trong Excel để lọc dữ liệu, cắt chuỗi. Hãy cùng Phanmemmienphiaz giải đáp thắc mắc của các bạn trong bài viết này nhé!
Cách dùng hàm mid trong Excel như thế nào? Hàm Mid là gì?
Hàm Mid là gì?
Cách dùng hàm Mid trong Excecl
Hàm MID trong Excel là hàm dùng để cắt chuỗi ký tự ở giữa chuỗi ký tự cho trước, được xác định bắt đầu bằng vị trí bạn chỉ định và số lượng ký tự theo ý bạn.
Trong đó:
- text: Chuỗi ký tự.
- m: Vị trí bắt đầu cần cắt chuỗi ký tự.
- n: Số ký tự cần cắt ra từ chuỗi ký tự.
Lưu ý:
- Nếu giá trị m lớn hơn độ dài chuỗi ký tự thì hàm MID trả về giá trị rỗng.
- Nếu giá trị m nhỏ hơn độ dài chuỗi ký tự nhưng tổng giá trị m cộng với n vượt quá độ dài chuỗi ký tự, thì hàm MID trả về các ký tự đến cuối cùng của chuỗi ký tự đã cho.
- Nếu giá trị m nhỏ hơn 1 hay giá trị n là số âm thì hàm MID trả về giá trị lỗi #VALUE!
Một vài ứng dụng thực tế của hàm MID – Cách dùng hàm mid trong Excel:
Ban đầu có một chuỗi ký tự là homnaytroimua.
– Yêu cầu lấy ra chữ naytroimua
=MID(C8,4,10)
-Yêu cầu lấy ra chữ mua
=MID(C8,11,3)
Lưu ý: Hàm MID, MIDB dùng cho các loại ngôn ngữ khác nhau:
- Các hàm này có thể không khả dụng ở mọi ngôn ngữ.
- Hàm MID luôn luôn đếm mỗi ký tự là 1.
- Hàm MIDB đếm mỗi ký tự là 2 khi bạn đã cho phép soạn thảo hỗ trợ DBCS (Ví dụ: Tiếng Nhật, Tiếng Trung, Tiếng Hàn), sau đó đặt nó làm ngôn ngữ mặc định. Nếu không, hàm MIDB đếm mỗi ký tự là 1.
Ví dụ về hàm Mid trong Excel
Để hiểu rõ hơn về cách sử dụng của hàm MID, mời bạn tham khảo qua các ví dụ dưới đây:
Cách xuất họ và tên bất kỳ
Dùng hàm SEARCH để tìm kiếm vị trí dấu cách. Sau đó, bạn dùng hàm MID để tá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.
Để xuất ra thông tin họ bất kỳ, ta có ví dụ:
=MID(A2,1,SEARCH(” “,A2)-1)
Để xuất tên ta dùng cấu trúc sau:
Lần này, SEARCH được sử dụng để xác định vị trí bắt đầu. Sau đó dùng hàm LEN để cung cấp tổng chiều dài của chuỗi ban đầu. 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. Tiếp đến, dùng hàm TRIM sẽ loại bỏ phần dấu cách.
=TRIM(MID(A2,SEARCH(” “,A2),LEN(A2))
Cách trích xuất chuỗi ký tự nằm giữa hai dấu cách
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.
Tiếp đến, để có được vị trí của khoảng cách thứ 2 thì bạn hãy 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)
Sau đó bạn cần trừ đi vị trí 2 khoảng không của 2 dấu cách. Sử dụng: SEARCH (” “, A2, SEARCH (” “,A2)+1) – SEARCH (” “,A2)
Kết hợp những công thức nêu trên lại với nhau chúng ta có được công thức MID để trích ra chuỗi ký tự nằm giữa hai dấu cách:
=MID(A2, SEARCH(” “,A2)+1, SEARCH (” “, A2, SEARCH (” “,A2)+1) – SEARCH (” “,A2)-1)
Cách tách chữ từ vị trí bất kỳ
Bạn sử dụng công thức chung 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.
- N là số thứ tự của từ cần trích xuất.
- LEN – hàm lấy tổng chiều dài của chuỗi văn bản gốc.
- REPT – hàm 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.
- TRIM – loại bỏ dấu cách.
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. 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)))
Tiếp đến, bạn cần tính ra vị trí bắt đầu của chuỗi con mà bạn cần trích xuất 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 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 bạn 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 đây, 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ách trích xuất một từ chứa một hoặc nhiều ký tự cụ thể
Cùng xem qua ví dụ sau: Lọc giá tiền chứa trong đoạn văn bản khi sử dụng hàm MID.
=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,99)),MAX(1,FIND(“$”,SUBSTITUTE(A2,” “,REPT(” “,99)))-50),99))
Giải thích:
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 sẽ tìm kiếm và 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.
Vì thế, 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 và trả về giá tiền kèm theo (40$, 600$).
Cách khiến hàm MID trả về một số
=VALUE(MID(A2,7,3))
Hàm MID trong Excel luôn trả về chuỗi văn bản, vậy để chuyển đầu ra thành một số. Bạn hãy sử dụng hàm VALUE để chuyển đổi.
Nguồn: Tham khảo Internet
Thông tin cần tư vấn truy cập phanmemmienphiaz.com để được hỗ trợ.
Bài viết Cách dùng hàm mid trong Excel để lọc dữ liệu, cắt chuỗi đã xuất hiện đầu tiên vào ngày Phần Mềm Miễn Phí AZ.
from Phần Mềm Miễn Phí AZ https://ift.tt/1GN8j7z
via IFTTT
Nhận xét
Đăng nhận xét