Hàm lấy giá trị giữa

Hàm MID vào Excel được dùng để cắt chuỗi ký kết tự chính giữa xâu ký tự cho trước. Hiện nay nay, nó được áp dụng khá phổ biến trong vượt trình đo lường và thống kê ở bảng tính Excel.

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

Bạn đang xem: Hàm lấy giá trị giữa


Cú pháp của hàm MID

=MID(text,m,n)

Trong đó:

+ text: Chuỗi cam kết tự.

+ m: Vị trí ban đầu cần cắt chuỗi ký tự.

+ n: Số ký tự đề nghị cắt ra từ chuỗi ký tự.

Ví dụ

1/ lấy ra 5 ký tự vào chuỗi đang cho bắt đầu từ địa điểm số4 với công thức: =MID(B3;4;5).

*

=> Kết quả:


*

*

2/ tại cột F, kéo ra 1 ký tự, bắt đầu từ địa điểm số 4 trong số ô trực thuộc cột Mã hộ vào bảng số liệu bên 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 các bạn không thể vội vã và rất cần được ghi nhớ một trong những điều nhằm tránh việc phạm phải những lỗi không đúng phổ biến.

Xem thêm: Những Bài Hát Dành Cho Vợ Yêu, Những Bài Hát Về Vợ Của Vũ Duy Khánh

Hàm MID luôn luôn trả về một chuỗi văn bản, trong cả khi chuỗi văn bạn dạng đó chỉ chứa toàn các chữ số. Điều này cực kỳ đáng chú ý nếu bạn có nhu cầu sử dụng công dụng của phép tính áp dụng hàm MID trong số phép tính khác. Để thay đổi đầu ra của hàm MID là một số trong những thì cần phối hợp hàm MID với hàm VALUE.Nếu start_num to hơn tổng chiều lâu năm của văn phiên bản gốc, bí quyết tính theo hàm MID vẫn trả về một chuỗi rỗng (“”).Nếu start_num nhỏ hơn 1, bí quyết tính của hàm MID trả về sẽ ảnh hưởng lỗi #VALUE.Nếu num_chars bé dại hơn 0 (số âm), phương pháp MID trả về #VALUE! nếu như num_chars bởi 0 đã trả về một chuỗi rỗng (ô rỗng).Nếu tổng của start_num cùng num_chars vượt vượt tổng chiều dài của chuối văn bạn dạng gốc, hàm Excel MID đang trả về một chuỗi cam kết tự bắt đầu từ start_num cho đến ký tự ở đầu cuối 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 thực hiện hàm MID trong Excel các bạn thường sẽ phải kết hợp nó với đa số hàm khác, như các ví dụ sau đây.

Cách xuất tên cùng họ

Chúng ta hoàn toàn có thể trích xuất họ, tên lần lượt bằng phương pháp sử dụng hàm LEFT và hàm RIGHT. Tuy nhiên, chúng ta cũng hoàn toàn có thể thực hiện nay được bằng cách thức khác.

Sử dụng hàm MID nhằm trích xuất họ

Giả sử, họ với 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, chúng ta 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 search được dùng để bỏ dấu biện pháp và trả lại vị trí của nó, vị thế chúng ta cũng có thể bỏ qua dấu giải pháp đó. Sau đó, bạn dùng hàm MID để trích ra chuỗi con ban đầu từ ký kết tự đầu tiên cho đến ký từ đứng trước dấu cách, từ kia ta nhận được họ.

*
vi-du-ham-mid-trong-excel-1

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ách làm sau:

=TRIM(MID(A2,SEARCH(” “,A2),LEN(A2)))

Lần này, tìm kiếm được sử dụng để xác xác định trí bước đầu (từ chỗ dấu cách). Không đề xuất tính chính xác vị trí ngừng (vì trường hợp start_num với 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ý tự sót lại trong chuỗi văn bạn dạng gốc). Bởi vậy, vào đối số num_chars, bạn chỉ việc cung cấp tổng chiều lâu năm của chuỗi thuở đầu được trả về bằng cách sử dụng hàm tác dụng LEN. Nếu không dùng hàm LEN thì chúng ta có thể đặt một số trong những thật dài cho chuỗi cam kết tự đề xuất trích xuất, ví dụ như 100. Cuối cùng hàm TRIM sẽ vứt bỏ phần dấu bí quyết và mang đến ra kết quả như sau:

*
vi-du-ham-mid-trong-excel-2

Cách trích xuất chuỗi cam kết tự nằm giữa hai lốt cách

Như ví dụ ở mặt trên, nếu ko kể họ với tên còn cất tên đệm thì làm nuốm nào new trích xuất được tên đệm đó? chúng ta cũng có thể thực hiện tại theo cách thức sau đây:

Giống như ở ví dụ trước, bọn họ vẫn sử dụng hàm search để xác xác định trí của dấu biện pháp đầu tiên, cộng thêm một vào đó để chuỗi ký tự trích ra ban đầu bằng ký kết tự đứng ngay lập tức sau lốt cách. Trường đoản cú đó, bạn sẽ có được thông số start_num từ cách làm MID: SEARCH(” “,A2)+1

Tiếp theo, để sở hữu được địa chỉ của khoảng cách thứ 2 thì họ sử dụng hàm search với việc bắt đầu tìm kiếm từ cam kết tự đầu tiên sau dấu giải pháp thứ 2: SEARCH(” “,A2,SEARCH(” “,A2)+1)

Để đạt được chuỗi cam kết tự trả về, bắt buộc 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) – search (” “,A2) phối kết hợp lại với nhau bọn họ có được công thức MID để trích ra chuỗi ký tự đứng giữa hai không gian gian tạo do hai vết cách:

=MID(A2, SEARCH(” “,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 công dụng trong hình hình ảnh bên dưới:

*
vi-du-ham-mid-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 biện pháp bất kỳ:

MID(string, SEARCH(delimiter, string)+1, tìm kiếm (delimiter, string, tìm kiếm (delimiter, string)+1) – search (delimiter, string)-1)

Ví dụ, để trích ra một chuỗi bé được phân cách bằng vết phẩy và khoảng cách, bạn thực hiện 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ự tác dụng của phương pháp này:

*
vi-du-ham-mid-trong-excel-4

Cách trích xuất từ máy N vào một chuỗi văn bản

Ví dụ này là một trong ví dụ điển hình cho việc phối hợp sáng tạo bí quyết MID, kết hợp của 5 hàm không giống nhau. LEN – để lấy được tổng chiều lâu năm của chuỗi văn bản gốc REPT – để tái diễn một ký tự rõ ràng với số lần nhất định SUBSTITUTE – thay thế sửa chữa một cam kết tự bằng một cam kết tự không giống MID – trích xuất một chuỗi con TRIM – loại trừ khoảng không khí dấu phương pháp thêm vào

Ta gồm công thức chung 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 muốn trích xuất. • N là số thứ tự của từ buộc phải trích xuất

Ví dụ, nhằm trích xuất từ thứ 2 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ó thể nhập số sản phẩm công nghệ tự từ yêu cầu trích xuất (N) trong vô số ô trong thuộc 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 vui chơi của công thức bên trên như sau

Về bản chất, công thức bao hàm những từ solo trong văn bản gốc và các từ bí quyết nhau bởi rất nhiều khoảng trống (dấu cách), để tìm được chuỗi tài liệu bạn đề nghị trong cả một khối những “khoảng trống-từ-khoảng trống” thì chúng ta cần phải loại trừ các không gian đó. Nói rõ ràng hơn thì công thức vận động theo súc tích dưới đây:

• Hàm SUBSTITUTE cùng REPT thay thế sửa chữa mỗi khoảng chừng trống hiếm hoi trong một chuỗi bằng nhiều khoảng chừng trống. Con số khoảng trống được thêm vào bởi tổng chiều nhiều năm 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 tác dụng mà bạn muốn 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ỏ” phiêu bạt trong một không gian rộng mập gồm: khoảng chừng trống-từ thiết bị nhất-khoảng trống-từ sản phẩm công nghệ hai-khoảng trống-từ sản phẩm ba-…. Trường đoản cú chuỗi văn bạn dạng như vậy bọn họ nhận được đối số của bí quyết MID.

• Tiếp đến, chúng ta tính ra vị trí ban đầ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ự thứ nhất trong chuỗi con bắt buộc trích xuất hoặc địa điểm của một trong những 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 dễ dàng và đơn giản nhất vì bạn cũng có thể tìm ra bằng cách lấy tổng chiều dài của chuỗi văn phiên bản gốc: LEN(A2). Qua đó, bạn sẽ loại bỏ được những không gian trong chuỗi dài tất cả 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 vận động tốt trong đa số các ngôi trường hợp nhưng nếu có từ 2 khoảng trống sát nhau giữa những từ thì hiệu quả nhận được sẽ ảnh hưởng sau. Để khắc phục và hạn chế 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 không nằm giữa 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 dưới đây:

*
vi-du-ham-mid-trong-excel-5

Nếu chuỗi văn bạn dạng gốc chứa được nhiều khoảng trống giữa những từ và các từ trong đó quá phệ 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á 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ừ cất một hoặc các ký tự thay thể

Ví dụ bên dưới đây cho thấy thêm sự hữu dụng của công thức MID trong Excel nhằm trích xuất một từ chứa một hoặc các 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 gốc nằm ở ô A2, bạn cần tìm kiếm chuỗi con chứa kí trường đoản cú “$” (giá cả) chúng ta dùng cách làm sau:

=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,99)),MAX(1,FIND(“$”,SUBSTITUTE(A2,” “,REPT(” “,99)))-50),99))