Hàm MID vào Excel được dùng làm cắt chuỗi ký tự ở giữa xâu ký tự cho trước. Hiện nay nay, nó được sử dụng khá phổ biến trong thừa trình tính toán và thống kê trong bảng tính Excel.
Bài viết bên dưới đây, Học Excel Online để giúp đỡ bạn nắm rõ hơn về cú pháp và cách áp dụng hàm MID. Bạn đang xem: Cách sử dụng hàm mid trong excel
Cú pháp của hàm MID
=MID(text,m,n)
– Trong đó:
+ text: Chuỗi ký tự.
+ m: Vị trí ban đầu cần cắt chuỗi ký tự.
+ n: Số cam kết tự yêu cầu cắt ra tự chuỗi cam kết tự.
Ví dụ1/ mang ra 5 ký tự trong chuỗi đang cho ban đầ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, lấy ra 1 cam kết tự, bắt đầu từ địa chỉ số 4 trong số ô trực thuộc cột Mã hộ trong bảng số liệu dưới đây.
=> Kết quả:
Những điều điều cần xem xét về hàm Excel MID
Khi áp dụng hàm MID chúng ta không thể nóng vội và cần phải ghi nhớ một số điều để tránh việc mắc phải những lỗi không đúng phổ biến.
Xem thêm: Vị Trí Mic Của Laptop Dell Nằm Ở Đâu, Vị Trí Micro Trên Laptop Dell
Hàm MID luôn trả về một chuỗi văn bản, trong cả khi chuỗi văn phiên bản đó chỉ đựng toàn các chữ số. Điều này hết sức đáng lưu ý nếu bạn có nhu cầu sử dụng kết quả của phép tính thực hiện hàm MID trong các phép tính khác. Để đổi khác đầu ra của hàm MID là một số thì cần kết hợp hàm MID với hàm VALUE.Nếu start_num lớn hơn tổng chiều lâu năm của văn bản gốc, bí quyết tính theo hàm MID vẫn trả về một chuỗi trống rỗng (“”).Nếu start_num bé dại hơn 1, cách làm tính của hàm MID trả về có khả năng sẽ bị lỗi #VALUE.Nếu num_chars nhỏ tuổi hơn 0 (số âm), phương pháp MID trả về #VALUE! trường hợp num_chars bằng 0 vẫn trả về một chuỗi rỗng (ô rỗng).Nếu tổng của start_num với num_chars vượt vượt tổng chiều nhiều năm của chuối văn phiên bản gốc, hàm Excel MID đang trả về một chuỗi ký kết tự bắt đầu từ start_num cho đến ký tự sau cuối trong chuỗi văn bản gốc đó.Các ví dụ áp dụng công thức tính của hàm MIDKhi áp dụng hàm MID vào Excel bạn thường sẽ phải kết hợp nó với các hàm khác, giống như các ví dụ sau đây.
Cách xuất tên và họ
Chúng ta rất có thể trích xuất họ, thương hiệu lần lượt bằng cách sử dụng hàm LEFT cùng hàm RIGHT. Mặc dù nhiên, họ cũng rất có thể thực hiện nay được bằng phương thức khác.
Sử dụng hàm MID để trích xuất họ
Giả sử, họ và tên không thiếu nằm trong ô A2, họ với tên được chia cách với nhau bằng dấu cách, bạn cũng có thể trích xuất họ bằng cách sử dụng cách làm sau:
=MID(A2,1,SEARCH(” “,A2)-1)
Chức năng search được dùng làm bỏ dấu bí quyết và trả lại địa điểm của nó, vì chưng thế chúng ta cũng có thể bỏ qua dấu biện pháp đó. Sau đó, chúng ta dùng hàm MID để trích ra chuỗi con bắt đầu từ ký tự đầu tiên cho đến ký trường đoản cú đứng trước vệt cách, từ đó ta chiếm được họ.
Sử dụng hàm MID để trích xuất tên
Để trích xuất tên từ ô A2, chúng ta sử dụng công thức sau:
=TRIM(MID(A2,SEARCH(” “,A2),LEN(A2)))
Lần này, search được thực hiện để xác xác định trí ban đầu (từ vị trí dấu cách). Không phải tính đúng mực vị trí dứt (vì nếu như start_num với num_chars cộng lại to hơn tổng chiều dài chuỗi thì hàm vẫn trả về tất cả các ký tự sót lại trong chuỗi văn bản gốc). Vị vậy, vào đối số num_chars, bạn chỉ việc cung cung cấp tổng chiều nhiều năm của chuỗi thuở đầu được trả về bằng phương pháp sử dụng hàm chức năng LEN. Còn nếu như không dùng hàm LEN thì bạn cũng có thể đặt một số thật dài cho chuỗi ký tự bắt buộc trích xuất, ví dụ như 100. Sau cùng hàm TRIM sẽ loại bỏ phần dấu giải pháp và cho ra tác dụng như sau:
Cách trích xuất chuỗi cam kết tự nằm trong lòng hai dấu cách
Như lấy một ví dụ ở bên trên, nếu xung quanh họ và tên còn chứa tên đệm thì làm nắm nào mới trích xuất được tên đệm đó? chúng ta có thể thực hiện tại theo phương pháp sau đây:
Giống như nghỉ ngơi ví dụ trước, họ vẫn sử dụng hàm search để xác định vị trí của dấu giải pháp đầu tiên, cộng thêm 1 vào đó nhằm chuỗi ký kết tự trích ra ban đầu bằng ký kết tự đứng tức thì 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ì bọn họ sử dụng hàm tìm kiếm với việc bắt đầu tìm kiếm từ ký tự trước tiên sau dấu biện pháp thứ 2: SEARCH(” “,A2,SEARCH(” “,A2)+1)
Để có được chuỗi ký tự trả về, buộc phải trừ đi địa điểm 2 không gian của 2 lốt cách. Từ bỏ đó, bọn họ có đối số num_chars: SEARCH (” “, A2, tìm kiếm (” “,A2)+1) – tìm kiếm (” “,A2) phối hợp lại với nhau họ có được cách làm MID nhằm trích ra chuỗi ký tự đứng thân hai khoảng không gian tạo bởi vì hai dấu cách:
=MID(A2, SEARCH(” “,A2)+1, tìm kiếm (” “, A2, search (” “,A2)+1) – tìm kiếm (” “,A2)-1)
Các bạn cũng có thể xem hiệu quả trong hình hình ảnh bên dưới:
Tương tự, chúng ta cũng có thể trích xuất một chuỗi con nằm trong lòng hai dấu biện pháp bất kỳ:
MID(string, SEARCH(delimiter, string)+1, search (delimiter, string, search (delimiter, string)+1) – search (delimiter, string)-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 chừ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 dưới để thấy sự kết quả của công thức này:
Cách trích xuất từ trang bị N trong một chuỗi văn bản
Ví dụ này là 1 ví dụ điển hình cho việc kết hợp sáng tạo bí quyết MID, phối hợp của 5 hàm khác nhau. LEN – để mang được tổng chiều dài của chuỗi văn bạn dạng gốc REPT – để lặp lại một cam kết tự ví dụ với số lần nhất định SUBSTITUTE – sửa chữa một ký tự bởi một ký kết tự không giống MID – trích xuất một chuỗi bé TRIM – vứt bỏ khoảng không gian dấu biện pháp thêm vào
Ta gồm công thức tầm thường 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 sơ chứa dữ liệu bạn có nhu cầu trích xuất. • N là số máy tự của từ đề nghị trích xuất
Ví dụ, nhằm trích xuất từ thứ 2 trong chuỗi văn bản ô A2, ta thực hiện công thức sau:
=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))
Hoặc, chúng ta cũng có thể nhập số đồ vật tự từ buộc phải trích xuất (N) trong vô số ô trong thuộc công thức, như hình chụp screen bên dưới:
Cách hoạt động vui chơi của công thức trên như sau
Về bạn dạng chất, công thức bao gồm những từ solo trong văn bạn dạng gốc và những từ giải pháp nhau bởi không hề ít 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 đào thải các không gian đó. Nói ví dụ hơn thì công thức chuyển động theo súc tích dưới đây:
• Hàm SUBSTITUTE và REPT sửa chữa thay thế mỗi khoảng tầm trống lẻ tẻ trong một chuỗi bởi nhiều khoảng tầm trống. Con số khoảng trống nhận 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 rất có thể coi hiệu quả mà bạn có nhu cầu trích ra trường đoản cú chuỗi văn bạn dạng gốc cũng tương tự một “hành tinh nhỏ” xiêu bạt trong một không gian rộng phệ gồm: khoảng tầm trống-từ thiết bị nhất-khoảng trống-từ lắp thêm hai-khoảng trống-từ đồ vật ba-…. Từ chuỗi văn bản như vậy họ nhận được đối số của cách làm MID.
• Tiếp đến, bạn tính ra vị trí ban đầu của chuỗi bé mà bạn cần trích xuất (đối số start_num) áp 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ự trước tiên trong chuỗi con nên trích xuất hoặc địa chỉ của một vài khoảng trống trong tổng số những không gian trước đó.
• Số cam kết tự trích xuất (đối số num_chars) là phần đơn giản dễ dàng nhất vì bạn cũng có thể tìm ra bằng cách lấy tổng chiều lâu năm của chuỗi văn bạn dạng gốc: LEN(A2). Qua đó, các bạn sẽ loại quăng quật được những khoảng trống trong chuỗi dài gồm cả không gian và từ.
• Cuối cùng, hàm TRIM sẽ loại bỏ khoảng trống nằm ở đầu cùng cuối. Công thức trên hoạt động tốt trong phần đông các ngôi trường hợp tuy thế nếu có từ 2 khoảng không sát nhau giữa các từ thì kết quả nhận được có khả năng sẽ bị sau. Để khắc phục và hạn chế 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 không gian nằm một trong những khoảng trống khác, tiến hành như sau:
=TRIM(MID(SUBSTITUTE(TRIM(A2),” “,REPT(” “,LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))
Các chúng ta theo dõi hình minh họa bên dưới đây:
Nếu chuỗi văn bạn dạng gốc đựng được nhiều khoảng trống giữa các từ và các từ trong số ấy quá béo hoặc quá nhỏ tuổi thì chúng ta nên thêm hàm TRIM lồng trong những hàm LEN nhằm 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 bảo vệ không xẩy ra lỗi.
Cách trích xuất một từ đựng một hoặc nhiều ký tự cụ thể
Ví dụ bên dưới đây cho biết sự hữu dụng của công thức MID vào Excel để trích xuất một từ cất một hoặc những 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 dạng gốc nằm tại vị trí ô A2, bạn phải tìm kiếm chuỗi bé chứa kí trường đoản cú “$” (giá cả) bạn dùng cách làm sau:
=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,99)),MAX(1,FIND(“$”,SUBSTITUTE(A2,” “,REPT(” “,99)))-50),99))
