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