CÁCH SỬ DỤNG HÀM MID

Hàm MID vào Excel được dùng làm cắt chuỗi ký kết tự trung tâm xâu ký kết tự cho trước. Hiện tại nay, nó được áp dụng khá thông dụng trong quá trình tính toán và thống kê trên bảng tính Excel.

Bài viết 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.


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 cắt chuỗi cam kết tự.

+ n: Số ký tự buộc phải cắt ra trường đoản cú chuỗi ký tự.

Ví dụ

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

*

=> Kết quả:


*

*

2/ tại cột F, lấy ra 1 ký tự, ban đầu từ vị trí số 4 trong cá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 chú ý về hàm Excel MID

Khi thực hiện hàm MID các bạn không thể nhanh lẹ và rất cần phải ghi nhớ một số trong những điều nhằm tránh việc mắc phải những lỗi không nên phổ biến.

Hàm MID 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 để ý nếu bạn muốn sử dụng tác dụng của phép tính áp dụng hàm MID trong số phép tính khác. Để biến đổi đầu ra của hàm MID là một số thì cần phối kết hợp hàm MID với hàm VALUE.Nếu start_num lớn hơn tổng chiều nhiều năm của văn bản gốc, phương pháp tính theo hàm MID đang trả về một chuỗi rỗng (“”).Nếu start_num nhỏ 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 bé dại hơn 0 (số âm), bí quyết MID trả về #VALUE! nếu 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 quá tổng chiều lâu năm của chuối văn bản 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ự sau cùng trong chuỗi văn bạn dạng gốc đó.Các ví dụ sử dụng công thức tính của hàm MID

Khi sử dụng hàm MID vào Excel các bạn thường đang phải phối kết hợp nó với số đông hàm khác, tựa như các ví dụ sau đây.

Cách xuất tên và họ

Chúng ta hoàn toàn 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 với hàm RIGHT. Mặc dù nhiên, bọn họ cũng hoàn toàn có thể thực hiện được bằng phương thức khác.

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

Giả sử, họ cùng tên tương đối đầy đủ nằm vào ô A2, họ và tên được phân 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 để bỏ dấu phương pháp và trả lại vị trí của nó, bởi vì thế bạn có thể bỏ qua dấu bí quyết đó. Sau đó, bạn dùng hàm MID để trích ra chuỗi con bắt đầu từ ký tự đầu tiên cho tới ký tự đứng trước vết cách, từ đó ta nhận được họ.

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

Sử dụng hàm MID để trích xuất tên

Để trích xuất thương hiệu từ ô A2, chúng ta sử dụng phương pháp 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 bắt buộc tính chính xác vị trí kết thúc (vì ví như start_num với num_chars cùng lại lớn hơn tổng chiều dài chuỗi thì hàm vẫn 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ì vậy, vào đối số num_chars, bạn chỉ cần cung cung cấp tổng chiều lâ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. Nếu như không dùng hàm LEN thì bạn có thể đặt một số thật dài mang lại chuỗi ký kết tự buộc phải trích xuất, ví như 100. Sau cùng hàm TRIM sẽ đào thải phần dấu biện pháp và mang đến ra hiệu quả như sau:

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

Cách trích xuất chuỗi ký tự nằm giữa hai vệt cách

Như lấy ví dụ như ở mặt trên, nếu kế bên họ cùng tên còn cất tên đệm thì làm cụ nào bắt đầu trích xuất được tên đệm đó? bạn có thể thực hiện theo cách thức sau đây:

Giống như ở ví dụ trước, chúng ta 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 1 vào đó để chuỗi cam kết tự trích ra bắt đầu bằng ký tự đứng tức thì sau dấu cách. Từ đó, các bạn sẽ có được tham số start_num từ công thức MID: SEARCH(” “,A2)+1

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

Để giành được chuỗi ký kết tự trả về, nên trừ đi địa điểm 2 không gian của 2 vết cách. Tự đó, họ có đối số num_chars: SEARCH (” “, A2, search (” “,A2)+1) – search (” “,A2) phối hợp lại cùng với nhau chúng ta có được công thức MID để trích ra chuỗi ký kết tự đứng giữa hai khoảng không gian tạo vì hai vết cách:

=MID(A2, SEARCH(” “,A2)+1, search (” “, A2, search (” “,A2)+1) – search (” “,A2)-1)

Các chúng ta cũng có thể xem kết quả trong hình ảnh bên dưới:

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

Tương tự, bạn cũng có thể trích xuất một chuỗi con nằm giữa hai dấu cách 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ụ, nhằm trích ra một chuỗi bé được ngăn cách bằng vệt 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 bên dưới để thấy sự tác dụng của công thức 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à 1 ví dụ nổi bật cho việc phối hợp sáng tạo công thức MID, phối hợp của 5 hàm không giống nhau. LEN – để đưa được tổng chiều lâu năm của chuỗi văn phiên bản gốc REPT – để tái diễn một ký kết tự rõ ràng với mốc giới hạn nhất định SUBSTITUTE – sửa chữa thay thế 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 – sa thải khoảng không khí dấu phương pháp 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ố lắp thêm tự của từ yêu cầu trích xuất

Ví dụ, để 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, chúng ta cũng có thể nhập số vật dụng tự từ yêu cầu trích xuất (N) trong nhiều ô trong cùng công thức, như hình chụp screen 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 trên như sau

Về bạn dạng chất, công thức bao gồm những từ 1-1 trong văn bản gốc và những từ phương 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 buộc phải 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 vứt bỏ các khoảng trống đó. Nói cụ thể hơn thì công thức chuyển động theo súc tích dưới đây:

• Hàm SUBSTITUTE với REPT sửa chữa thay thế mỗi khoảng tầm trống riêng biệt trong một chuỗi bằng nhiều khoảng tầm 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 dạng gốc được trả về bới hàm LEN: SUBSTITUTE(A2,” “,REPT(” “,LEN(A2)))

Bạn hoàn toàn có thể coi công dụng mà bạn có nhu cầu trích ra từ chuỗi văn phiên bản gốc cũng tương tự một “hành tinh nhỏ” phiêu bạt trong một không khí rộng lớn gồm: khoảng chừng trống-từ trang bị nhất-khoảng trống-từ máy hai-khoảng trống-từ lắp thêm ba-…. Trường đoản cú chuỗi văn bạn dạng như vậy chúng ta nhận được đối số của bí quyết MID.

• Tiếp đến, các 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) 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ự thứ nhất trong chuỗi con đề xuất trích xuất hoặc địa chỉ của một số khoảng trống trong tổng thể những khoảng không trước đó.

• Số ký kết tự trích xuất (đối số num_chars) là phầ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 dài của chuỗi văn bản gốc: LEN(A2). Qua đó, bạn sẽ loại quăng quật được những khoảng trống trong chuỗi dài tất cả cả khoảng trống và từ.

• Cuối cùng, hàm TRIM sẽ loại trừ khoảng trống nằm ở đầu với cuối. Cách làm trên chuyển động tốt trong phần nhiều các trường hợp tuy vậy nếu gồm từ 2 khoảng trống sát nhau giữa những từ thì công dụng nhận được sẽ bị sau. Để khắc phục và hạn chế lỗi không đúng này thì nên lồng ghép hàm TRIM vào hàm SUBSTITUTE để loại trừ các khoảng khô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 bên dưới đây:

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

Nếu chuỗi văn bản gốc chứa đựng nhiều khoảng trống giữa những từ và các từ trong đó quá to 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òn có vẻ khá tinh vi nhưng lại đảm bảo không xẩy ra lỗi.

Cách trích xuất một từ đựng một hoặc các ký tự cố thể

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

Leave a Reply

Your email address will not be published. Required fields are marked *