29 C
Hanoi
Thursday, September 16, 2021
Trang chủCông thức ExcelCách sử dụng hàm MID trong Excel để tách chuỗi

Cách sử dụng hàm MID trong Excel để tách chuỗi

Join LeQuocThai.Com on Telegram Channel

Đánh giá:

0 / 5 Voted: 5 Number: 2

Your page rank:

Excel là phần mềm được sử dụng phổ biến hiện nay, một trong những hàm cơ bản của Excel là hàm MID, hàm LEN được dùng để cắt chuỗi. Có thể dùng kết hợp với hàm LEN, MID, Find/FindB, Search/Seardb, LEFT/LEFTB

Lequocthai.com thực hiện trên Excel 2019, bạn có thể áp dụng tương tự trên các phiên bản Excel khác như: 2007, 2010, 2013, 2016 và Microsoft Excel 365.

Hàm MID trong Excel

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. Hàm MID có cấu trúc như sau:

=MID(text,m,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: 

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ụ hàm MID 

Để 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/FINDB 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.

Ok, vậy là chúng ta đã lược sơ qua về hàm MIDlequocthai.com mong sẽ giúp ích cho bạn trong việc thao tác với dữ liệu nhé! Bye.

Join LeQuocThai.Com on Telegram Channel

Lê Quốc Tháihttps://lequocthai.com/
Yep! I am Le Quoc Thai codename name tnfsmith, one among of netizens beloved internet precious, favorite accumulate sharing all my knowledge and experience Excel, PC tips tricks, gadget news during over decades working in banking data analysis.

BÌNH LUẬN

Vui lòng nhập bình luận của bạn
Vui lòng nhập tên của bạn ở đây

Join LeQuocThai.Com on Telegram Channel

Đọc nhiều nhất

BÀI VIẾT MỚI NHẤT

CÙNG CHỦ ĐỀ