Công Thức Lấy Ký Tự Giữa Trong Excel

Hàm MID vào Excel được dùng để cắt chuỗi cam kết từ chính giữa xâu ký từ bỏ mang lại trước. Lúc này, nó được thực hiện khá phổ cập vào quy trình tính toán thù với những thống kê ở bảng tính Excel.

Bài viết dưới đây, Học Excel Online sẽ giúp đỡ chúng ta nắm rõ hơn về cú pháp với giải pháp áp dụng hàm MID.

Bạn đang xem: Công thức lấy ký tự giữa trong excel


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

=MID(text,m,n)

Trong đó:

+ text: Chuỗi ký từ bỏ.

+ m: Vị trí ban đầu buộc phải cắt chuỗi cam kết từ bỏ.

+ n: Số cam kết trường đoản cú cần giảm ra từ chuỗi cam kết từ.

Ví dụ

1/ Lấy ra 5 ký từ trong chuỗi đã mang lại bắt đầ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, mang ra 1 ký tự, bước đầu từ bỏ vị trí số 4 trong các ô nằm trong cột Mã hộ trong bảng số liệu sau đây.

*

=> Kết quả:

*

Những điều vấn đề cần chú ý về hàm Excel MID

Khi thực hiện hàm MID các bạn bắt buộc nhanh chóng cùng cần được ghi nhớ một vài điều nhằm tách vấn đề mắc phải rất nhiều lỗi không đúng phổ biến.

Xem thêm: Top 8 Địa Chỉ Khám Thần Kinh Ở Đâu Tốt Nhất Tphcm ? Khám Thần Kinh Ở Đâu Tốt Tp Hcm

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

Lúc áp dụng hàm MID vào Excel các bạn hay đang bắt buộc kết hợp nó cùng với đầy đủ hàm khác, giống như những ví dụ dưới đây.

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

Chúng ta rất có thể trích xuất chúng ta, thương hiệu lần lượt bằng phương pháp áp dụng hàm LEFT cùng hàm RIGHT. Tuy nhiên, họ cũng rất có thể triển khai được bởi cách thức khác.

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

Giả sử, bọn họ cùng thương hiệu không hề thiếu phía trong ô A2, chúng ta cùng thương hiệu được phân làn cùng nhau bởi vệt phương pháp, chúng ta có thể trích xuất họ bằng cách thực hiện công thức sau:

=MID(A2,1,SEARCH(” “,A2)-1)

Chức năng SEARCH được dùng để làm vứt dấu biện pháp và trả lại vị trí của chính nó, chính vì vậy chúng ta có thể bỏ qua lốt cách đó. Sau đó, chúng ta sử dụng hàm MID để trích ra chuỗi bé ban đầu từ ký kết tự thứ nhất cho đến ký tự đứng trước vết bí quyết, từ đó ta chiếm được chúng ta.

*
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 trường đoản cú ô A2, chúng ta thực hiện bí quyết sau:

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

Lần này, SEARCH được thực hiện nhằm xác xác định trí bước đầu (từ bỏ địa điểm dấu cách). Không cần tính chính xác địa điểm xong xuôi (vì chưng trường hợp start_num với num_chars cùng lại to hơn tổng chiều lâu năm chuỗi thì hàm đang trả về tất cả những ký trường đoản cú còn lại vào chuỗi văn uống bản gốc). Vì vậy, vào đối số num_chars, chúng ta chỉ việc cung ứng tổng chiều lâu năm của chuỗi ban sơ được trả về bằng cách sử dụng hàm tính năng LEN. Nếu ko dùng hàm LEN thì bạn có thể đặt một số thiệt dài mang lại chuỗi ký tự phải trích xuất, ví như 100. Cuối thuộc hàm TRIM vẫn thải trừ phần vết bí quyết cùng tạo ra tác dụng nlỗi sau:

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

Cách trích xuất chuỗi cam kết trường đoản cú nằm trong lòng nhị dấu cách

Nhỏng ví dụ nghỉ ngơi trên, trường hợp quanh đó bọn họ cùng tên còn chứa tên đệm thì làm cho ráng làm sao bắt đầu trích xuất được thương hiệu đệm đó? quý khách hàng có thể tiến hành theo phương pháp sau đây:

Giống nhỏng làm việc ví dụ trước, bọn họ vẫn thực hiện hàm SEARCH để xác định vị trí của dấu giải pháp trước tiên, cùng thêm 1 vào đó nhằm chuỗi cam kết từ trích ra bước đầu bởi ký tự đứng ngay sau lốt cách. Từ đó, bạn sẽ giành được tsay đắm số start_num từ bỏ phương pháp MID: SEARCH(” “,A2)+1

Tiếp theo, để có được vị trí của khoảng cách thứ 2 thì bọn họ thực hiện hàm SEARCH cùng với vấn đề bắt đầu tra cứu kiếm trường đoản cú ký từ đầu tiên sau lốt phương pháp sản phẩm công nghệ 2: SEARCH(” “,A2,SEARCH(” “,A2)+1)

Để đạt được chuỗi cam kết tự trả về, yêu cầu trừ đi vị trí 2 không gian của 2 vết bí quyết. Từ kia, chúng ta gồm đối số num_chars: SEARCH (” “, A2, SEARCH (” “,A2)+1) – SEARCH (” “,A2) Kết vừa lòng lại cùng nhau bọn họ đã đạt được công thức MID nhằm trích ra chuỗi cam kết từ đứng thân nhị không gian gian sản xuất vì nhị lốt cách:

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

Các bạn cũng có thể coi tác dụng vào hình hình ảnh mặt 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 vết phương pháp bất kỳ:

MID(string, SEARCH(delimiter, string)+1, SEARCH (delimiter, string, SEARCH (delimiter, string)+1) – SEARCH (delimiter, string)-1)

ví dụ như, để trích ra một chuỗi nhỏ được chia cách bởi dấu phẩy cùng khoảng cách, các bạn sử dụng phương pháp sau:

=MID(A2,SEARCH(“, “,A2)+1,SEARCH(“, “,A2,SEARCH(“, “,A2)+1)-SEARCH(“, “,A2)-1)

Xem hình bên dưới giúp xem sự hiệu quả của bí quyết này:

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

Cách trích xuất tự lắp thêm N vào một chuỗi văn bản

lấy ví dụ này là 1 trong ví dụ điển hình mang đến Việc kết hợp sáng chế công thức MID, phối kết hợp của 5 hàm không giống nhau. LEN – để đưa được tổng chiều dài của chuỗi văn uống phiên bản gốc REPT – nhằm lặp lại một ký tự rõ ràng cùng với số lần nhất mực SUBSTITUTE – thay thế sửa chữa một cam kết tự bởi một ký từ bỏ khác MID – trích xuất một chuỗi bé TRIM – vứt bỏ không gian gian vết biện pháp thêm vào

Ta có bí quyết 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 uống bạn dạng thuở đầu chứa dữ liệu bạn có nhu cầu trích xuất. • N là số sản phẩm công nghệ trường đoản cú của từ bắt buộc trích xuất

lấy ví dụ, nhằm trích xuất tự thứ 2 vào chuỗi văn phiên bản ô A2, ta thực hiện cách làm sau:

=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))

Hoặc, bạn có thể nhập số đồ vật từ tự bắt buộc trích xuất (N) trong không ít ô trong thuộc cách làm, nhỏng hình chụp màn hình hiển thị mặt dưới:

*
trich-xuat-tu-trong-chuoi-van-ban

Cách hoạt động của cách làm bên trên nhỏng sau

Về bản chất, công thức bao gồm đa số tự đối kháng trong văn bản gốc với các tự bí quyết nhau vày không hề ít khoảng không (lốt cách), để kiếm được chuỗi tài liệu bạn phải vào cả một kăn năn các “khoảng chừng trống-từ-khoảng trống” thì họ cần được sa thải những khoảng không đó. Nói cụ thể hơn thế thì công thức chuyển động theo xúc tích và ngắn gọn dưới đây:

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

• Tiếp mang lại, bạn tính ra địa điểm ban đầu của chuỗi nhỏ cơ mà bạn phải trích xuất (đối số start_num) áp dụng phương thơm trình sau: (N-1) * LEN (A1) +1. Phép tính này trả về ví trí của ký kết từ bỏ trước tiên vào chuỗi nhỏ đề nghị trích xuất hoặc vị trí của một số khoảng không vào tổng thể phần đông khoảng không trước kia.

• Số cam kết từ trích xuất (đối số num_chars) là phần đơn giản tuyệt nhất bởi vì chúng ta cũng có thể đưa ra bằng cách rước tổng chiều lâ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 đầy đủ khoảng trống vào chuỗi lâu năm gồm cả khoảng trống với từ.

• Cuối cùng, hàm TRIM vẫn đào thải khoảng không nằm ở đầu cùng cuối. Công thức bên trên chuyển động tốt vào phần lớn những trường hòa hợp dẫu vậy trường hợp gồm từ 2 khoảng trống gần kề nhau giữa những từ bỏ thì công dụng cảm nhận sẽ ảnh hưởng sau. Để hạn chế lỗi không nên này thì nên lồng ghxay hàm TRIM vào hàm SUBSTITUTE để thải trừ các khoảng trống nằm giữa những khoảng không khác, thực hiện nhỏng sau:

=TRIM(MID(SUBSTITUTE(TRIM(A2),” “,REPT(” “,LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))

Các bạn theo dõi hình minc họa bên dưới đây:

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

Nếu chuỗi vnạp năng lượng phiên bản gốc chứa nhiều khoảng không thân những từ và các từ bỏ trong đó quá rộng hoặc quá bé dại thì bạn nên thêm hàm TRIM lồng trong mỗi hàm LEN để rời 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 tuy vậy lại đảm bảo an toàn ko xảy ra lỗi.

Cách trích xuất một từ bỏ chứa một hoặc nhiều ký từ vắt thể

ví dụ như dưới phía trên cho thấy sự có lợi của phương pháp MID vào Excel nhằm trích xuất một từ bỏ đựng một hoặc những ký trường đoản cú từ bỏ vnạp năng lượng phiê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 uống phiên bản nơi bắt đầu nằm ở ô A2, bạn phải tìm kiếm tìm chuỗi nhỏ cất kí trường đoản cú “$” (giá chỉ cả) bạn cần sử dụng phương pháp sau:

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