Hướng dẫn how do i extract text from the middle of a cell in excel? - làm cách nào để trích xuất văn bản từ giữa ô trong excel?

MID là một trong những chức năng văn bản mà Microsoft Excel cung cấp cho việc thao tác các chuỗi văn bản. Ở cấp độ cơ bản nhất, nó được sử dụng để trích xuất một chuỗi con từ giữa chuỗi văn bản. Trong hướng dẫn này, chúng tôi sẽ thảo luận về cú pháp và tính đặc hiệu của hàm giữa Excel, và sau đó bạn sẽ tìm hiểu một vài cách sử dụng sáng tạo để hoàn thành các nhiệm vụ đầy thách thức.

Chức năng Mid Excel - Syntax và sử dụng cơ bản

Nói chung, hàm giữa trong Excel được thiết kế để kéo một chuỗi con từ giữa chuỗi văn bản gốc. Về mặt kỹ thuật, hàm MID trả về số lượng ký tự được chỉ định bắt đầu tại vị trí bạn chỉ định.

Hàm Mid Excel có các đối số sau:

Mid (văn bản, start_num, num_chars)

Where:

• Văn bản là chuỗi văn bản gốc. is the original text string.
• Start_num là vị trí của nhân vật đầu tiên mà bạn muốn trích xuất. is the position of the first character that you want to extract.
• Num_chars là số lượng ký tự để trích xuất. is the number of characters to extract.

Tất cả 3 đối số là bắt buộc.

Ví dụ: để kéo 7 ký tự từ chuỗi văn bản trong A2, bắt đầu với ký tự thứ 8, hãy sử dụng công thức này:

=MID(A2,8, 7)

Kết quả có thể trông giống như thế này:

5 điều bạn nên biết về chức năng Mid Excel

Như bạn vừa thấy, không có khoa học tên lửa nào trong việc sử dụng hàm mid trong Excel. Và ghi nhớ các sự kiện đơn giản sau đây sẽ giữ cho bạn an toàn trước hầu hết các lỗi phổ biến.

1. Hàm MID luôn trả về một chuỗi văn bản, ngay cả khi chuỗi con được trích xuất chỉ chứa các chữ số. Điều này có thể rất quan trọng nếu bạn muốn sử dụng kết quả của công thức giữa của bạn trong các tính toán khác. Để chuyển đổi đầu ra thành một số, hãy sử dụng MID kết hợp với hàm giá trị như trong ví dụ này.text string, even if the extracted substring contains only digits. This may be critical if you wish to use the result of your Mid formula within other calculations. To convert an output into a number, use MID in combination with the VALUE function as shown in this example.
2. Nếu start_num lớn hơn chiều dài tổng thể của văn bản gốc, công thức mid excel trả về một chuỗi trống ("").

1. Nếu start_num nhỏ hơn 1, một công thức trung bình trả về #Value! lỗi.
2. Nếu num_chars nhỏ hơn 0 (số âm), công thức trung bình trả về #Value! lỗi. Nếu num_chars bằng 0, nó sẽ xuất ra một chuỗi trống (ô trống).
3. Nếu tổng của start_num và num_chars vượt quá tổng chiều dài của chuỗi gốc, hàm mid excel sẽ trả về một chuỗi con bắt đầu từ start_num và lên đến ký tự cuối cùng.

Chức năng giữa Excel - Ví dụ về công thức

Khi xử lý các nhiệm vụ thực tế trong Excel, bạn thường sẽ cần sử dụng MID kết hợp với các chức năng khác như được thể hiện trong các ví dụ sau.

Cách trích xuất tên đầu tiên và họ

Nếu bạn đã có cơ hội đọc các hướng dẫn gần đây của chúng tôi, bạn đã biết cách kéo tên đầu tiên bằng hàm bên trái và lấy tên cuối cùng với chức năng bên phải. Nhưng như thường lệ trong Excel, điều tương tự có thể được thực hiện theo nhiều cách khác nhau.

Công thức giữa để có được tên đầu tiên

Giả sử tên đầy đủ có trong ô A2, tên đầu tiên và tên cuối cùng được phân tách bằng một ký tự không gian, bạn có thể kéo tên đầu tiên bằng công thức này:

=MID(A2,1,SEARCH(" ",A2)-1)

Hàm tìm kiếm được sử dụng để quét chuỗi ban đầu cho ký tự không gian ("") và trả về vị trí của nó, từ đó bạn trừ 1 để tránh các khoảng trắng. Và sau đó, bạn sử dụng hàm mid để trả về một bộ nền bắt đầu bằng ký tự nắm tay và lên đến ký tự trước không gian, do đó tìm được tên đầu tiên.

Công thức giữa để có được họ

Để trích xuất tên cuối cùng từ A2, hãy sử dụng công thức này:

=TRIM(MID(A2,SEARCH(" ",A2),LEN(A2)))

Một lần nữa, bạn sử dụng chức năng tìm kiếm để xác định vị trí bắt đầu (một không gian). Chúng tôi không cần phải tính toán chính xác vị trí cuối (như bạn nhớ, nếu start_num và num_chars kết hợp lớn hơn tổng chiều dài chuỗi, tất cả các ký tự còn lại được trả về). Vì vậy, trong đối số Num_chars, bạn chỉ cần cung cấp tổng chiều dài của chuỗi ban đầu được trả về bởi hàm LEN. Thay vì Len, bạn có thể đặt một số đại diện cho họ dài nhất mà bạn mong đợi tìm thấy, ví dụ 100. Cuối cùng, hàm trang trí sẽ loại bỏ các không gian bổ sung và bạn nhận được kết quả sau:

Cách nhận chất nền giữa 2 phân định

Lấy ví dụ trước đó đi xa hơn, nếu bên cạnh tên đầu tiên và tên cuối cùng A2 cũng chứa một tên đệm, làm thế nào để bạn trích xuất nó?

Về mặt kỹ thuật, nhiệm vụ tập trung vào việc thực hiện các vị trí của hai không gian trong chuỗi ban đầu và bạn có thể thực hiện nó theo cách này:

• Giống như trong ví dụ trước, sử dụng chức năng tìm kiếm để xác định vị trí của không gian đầu tiên (""), mà bạn thêm 1 vì bạn muốn bắt đầu với ký tự theo không gian. Do đó, bạn nhận được đối số start_num về công thức giữa của bạn: Tìm kiếm ("", A2) +1start_num argument of your Mid formula: SEARCH(" ",A2)+1
• Tiếp theo, hãy lấy vị trí của ký tự không gian thứ 2 bằng cách sử dụng các hàm tìm kiếm lồng nhau hướng dẫn Excel bắt đầu tìm kiếm từ lần xuất hiện thứ 2 của ký tự không gian: Tìm kiếm ("", A2, Search ("", A2) +1)

  Để tìm ra số lượng ký tự để quay lại, hãy trừ vị trí của không gian thứ nhất khỏi vị trí của không gian thứ 2, sau đó trừ 1 khỏi kết quả vì bạn không muốn bất kỳ khoảng trống nào trong phần kết quả. Do đó, bạn có đối số num_chars: search ("", a2, search ("", a2) +1) - search ("", a2)num_chars argument: SEARCH (" ", A2, SEARCH (" ",A2)+1) - SEARCH (" ",A2)

Với tất cả các đối số được đặt cùng nhau, đây là công thức giữa Excel để trích xuất một chuỗi con giữa 2 ký tự không gian:

= Mid (a2, search ("", a2) +1, search ("", a2, search ("", a2) +1) - search ("", a2) -1)

Ảnh chụp màn hình sau đây cho thấy kết quả:

Theo cách tương tự, bạn có thể trích xuất một chuỗi con giữa bất kỳ dấu phân cách nào khác:

MID (Chuỗi, Tìm kiếm (Delimiter, String) +1, Search (Delimiter, String, Search (Delimiter, String) +1) - Search (Delimiter, String) -1)

Ví dụ: để kéo một chuỗi con được phân tách bằng dấu phẩy và không gian, hãy sử dụng công thức này:

= Mid (a2, search (",", a2)+1, search (",", a2, search (",", a2) +1) -search (",", a2) -1)

Trong ảnh chụp màn hình sau, công thức này được sử dụng để trích xuất trạng thái và nó thực hiện công việc một cách hoàn hảo:

Cách trích xuất từ ​​thứ n từ chuỗi văn bản

Ví dụ này cho thấy việc sử dụng sáng tạo của một công thức trung bình phức tạp trong Excel, bao gồm 5 chức năng khác nhau:

• Len - để có được tổng chiều dài chuỗi.
• Rept - Lặp lại một ký tự cụ thể một số lần nhất định.
• Thay thế - Thay thế một ký tự bằng một ký tự khác.
• Mid - Trích xuất một chuỗi con.
• Trim - Tháo các không gian bổ sung.

Công thức chung như sau:

Trim (mid (thay thế (chuỗi, "", rept ("", len (chuỗi))), (n-1)*len (chuỗi) +1, len (chuỗi)))

Where:

• Chuỗi là chuỗi văn bản gốc mà bạn muốn trích xuất từ ​​mong muốn.
• N là số lượng từ được trích xuất.

Chẳng hạn, để lấy từ thứ 2 từ chuỗi trong A2, hãy sử dụng công thức này:

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

Hoặc, bạn có thể nhập số lượng từ để trích xuất (n) vào một số ô và tham chiếu ô đó vào công thức của bạn, như được hiển thị trong ảnh chụp màn hình bên dưới:

Cách thức hoạt động của công thức này

Về bản chất, công thức kết thúc từng từ trong chuỗi ban đầu với nhiều không gian, tìm thấy khối "không gian không gian" không gian "mong muốn, trích xuất nó, sau đó loại bỏ thêm không gian. Để cụ thể hơn, công thức hoạt động với logic sau:

• Các hàm thay thế và Rept thay thế từng không gian trong chuỗi bằng nhiều khoảng trống. Số lượng không gian bổ sung bằng tổng chiều dài của chuỗi ban đầu được trả về bởi LEN: thay thế (A2, "", Rept ("", Len (A2)))

  Bạn có thể nghĩ về một kết quả trung gian khi "tiểu hành tinh" của các từ trôi trong không gian, như thế này: Spaces-Word1-Space-Word2-Space-Word3- Chuỗi "rộng rãi" này được cung cấp cho đối số văn bản của công thức giữa của chúng tôi.text argument of our Mid formula.

• Tiếp theo, bạn thực hiện vị trí bắt đầu của phần phụ của lợi ích (đối số start_num) bằng cách sử dụng phương trình sau: (n-1)*len (A1) +1. Tính toán này trả về vị trí của ký tự đầu tiên của từ mong muốn hoặc, thường xuyên hơn, vị trí của một số ký tự không gian trong sự phân tách không gian trước.start_num argument) using the following equation: (N-1)*LEN(A1)+1. This calculation returns either the position of the first character of the desired word or, more often, the position of some space character in the preceding space separation.
• Số lượng ký tự để trích xuất (đối số Num_chars) là phần dễ nhất - bạn chỉ cần lấy tổng độ dài của chuỗi gốc: Len (A2). Tại thời điểm này, bạn còn lại với phần phụ không gian từ không gian.num_chars argument) is the easiest part - you simply take the overall length of the original string: LEN(A2). At this point, you are left with spaces-desired word-spaces substring.
• Cuối cùng, chức năng trang trí được loại bỏ các không gian dẫn đầu và dấu vết.

Công thức trên hoạt động tốt trong hầu hết các tình huống. Tuy nhiên, nếu có 2 hoặc hơn liên tiếp giữa các từ, nó sẽ mang lại kết quả sai. Để khắc phục điều này, hãy làm tổ một hàm trang trí khác bên trong thay thế để loại bỏ các khoảng trống ở giữa ngoại trừ một ký tự không gian giữa các từ, như thế này:consecutive spaces between words, it yields wrong results. To fix this, nest another TRIM function inside SUBSTITUTE to remove excess in-between spaces except for a single space character between words, like this:

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

Ảnh chụp màn hình sau đây cho thấy công thức cải tiến trong hành động:

Nếu các chuỗi nguồn của bạn chứa nhiều khoảng trống giữa các từ cũng như các từ rất lớn và rất nhỏ, cũng được nhúng một hàm trang trí vào mỗi LEN, chỉ để giữ bạn ở phía an toàn:

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

Tôi đồng ý rằng công thức này trông hơi cồng kềnh, nhưng nó hoàn hảo xử lý tất cả các loại chuỗi.

Cách trích xuất một từ chứa (các) ký tự cụ thể

Ví dụ này cho thấy một công thức mid excel không tầm thường khác kéo một từ chứa (các) ký tự cụ thể từ bất cứ nơi nào trong chuỗi văn bản gốc:

Trim (mid (thay thế (chuỗi, "", rept ("", 99)), max (1, find (char, thay thế (chuỗi, "", rept ("", 99)))-50), 99) )

Giả sử văn bản gốc có trong ô A2 và bạn đang tìm cách có được một chuỗi con chứa ký tự "$" (giá), công thức có hình dạng sau:

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

Theo cách tương tự, bạn có thể trích xuất các địa chỉ email (dựa trên tên "@" char), tên trang web (dựa trên "www"), v.v.

Cách thức hoạt động của công thức này

Về bản chất, công thức kết thúc từng từ trong chuỗi ban đầu với nhiều không gian, tìm thấy khối "không gian không gian" không gian "mong muốn, trích xuất nó, sau đó loại bỏ thêm không gian. Để cụ thể hơn, công thức hoạt động với logic sau:

Hàm tìm xác định vị trí của ký tự mong muốn ($ trong ví dụ này), từ đó bạn trừ 50. Điều này đưa bạn 50 ký tự trở lại và đặt ở đâu đó vào giữa khối 99 không gian đi trước chuỗi con chứa ký tự được chỉ định.

Hàm MAX được sử dụng để xử lý tình huống khi bộ nền mong muốn xuất hiện ở đầu chuỗi văn bản gốc. Trong trường hợp này, kết quả của find ()-50 sẽ là một số âm và tối đa (1, find ()-50) thay thế nó bằng 1.

Từ điểm bắt đầu đó, hàm mid thu thập 99 ký tự tiếp theo và trả về phần phụ của sự quan tâm được bao quanh bởi rất nhiều không gian, như thế này: không gian không gian không gian. Như thường lệ, chức năng trang trí giúp bạn loại bỏ các không gian bổ sung.

Mẹo. Nếu chất nền được trích xuất là rất lớn, hãy thay thế 99 và 50 bằng số lượng lớn hơn, giả sử 1000 và 500. If the substring to be extracted is very big, replace 99 and 50 with bigger numbers, say 1000 and 500.

Cách buộc một công thức mid excel trả về một số

Giống như các chức năng văn bản khác, Excel Mid luôn trả về một chuỗi văn bản, ngay cả khi nó chỉ chứa các chữ số và trông giống như một số. Để biến đầu ra thành một số, chỉ cần "warp" công thức giữa của bạn thành hàm giá trị chuyển đổi giá trị văn bản biểu thị một số thành một số.

Ví dụ: để trích xuất một chuỗi con 3 Char

=VALUE(MID(A2,7,3))

Ảnh chụp màn hình dưới đây cho thấy kết quả. Vui lòng nhận thấy các số được liên kết bên phải được kéo vào cột B, trái ngược với các chuỗi văn bản liên kết bên trái ban đầu trong cột A:

Cách tiếp cận tương tự cũng hoạt động cho các công thức phức tạp hơn là tốt. Trong ví dụ trên, giả sử các mã lỗi có độ dài thay đổi, bạn có thể trích xuất chúng bằng công thức giữa có phần phụ giữa 2 dấu phân cách, được lồng trong hàm giá trị:

=VALUE(MID(A2,SEARCH(":",A2)+1,SEARCH(":",A2,SEARCH(":",A2)+1)-SEARCH(":",A2)-1))

Đây là cách bạn sử dụng hàm giữa trong Excel. Để hiểu rõ hơn về các công thức được thảo luận trong hướng dẫn này, bạn có thể tải xuống một sổ làm việc mẫu dưới đây. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Tải xuống Sổ làm việc thực hành

Chức năng Mid Excel - Ví dụ công thức (tệp .xlsx)

Nhiều ví dụ về việc sử dụng hàm giữa trong Excel: