Explanation
Ở lõi, công thức này sử dụng hàm giữa để trích xuất các ký tự bắt đầu từ không gian thứ hai đến cuối cùng. Hàm mid có 3 đối số: văn bản để làm việc, vị trí bắt đầu và số lượng ký tự để trích xuất.
Văn bản đến từ cột B và số lượng ký tự có thể là bất kỳ số lượng lớn nào sẽ đảm bảo hai từ cuối được trích xuất. Thách thức là xác định vị trí bắt đầu, chỉ sau không gian thứ hai đến cuối cùng. Công việc thông minh được thực hiện chủ yếu với hàm thay thế, có một đối số tùy chọn gọi là số phiên bản. Tính năng này được sử dụng để thay thế không gian thứ hai đến cuối cùng trong văn bản bằng ký tự "@", sau đó được đặt với hàm tìm.
Làm việc từ trong ra ngoài, đoạn trích bên dưới tìm ra tổng số không gian trong tổng số văn bản, từ đó 1 được trừ đi.
LEN[B5]-LEN[SUBSTITUTE[B5," ",""]]-1
Trong ví dụ được hiển thị, có 5 khoảng trắng trong văn bản, vì vậy mã trên trả về 4. Số này được đưa vào hàm thay thế bên ngoài dưới dạng số ví dụ:
SUBSTITUTE[B5," ","@",4]
Điều này khiến thay thế để thay thế ký tự không gian thứ tư bằng "@". Sự lựa chọn của @ là tùy ý. Bạn có thể sử dụng bất kỳ ký tự nào sẽ không xuất hiện trong văn bản gốc.
Tiếp theo, tìm vị trí ký tự "@" trong văn bản:
FIND["@","A stitch in time@saves nine"]
Kết quả của tìm thấy là 17, trong đó 1 được thêm vào để có được 18. Đây là vị trí bắt đầu và đi vào hàm giữa làm đối số thứ hai. Để đơn giản, số lượng ký tự để trích xuất được mã hóa cứng là 100. Số này tùy ý và có thể được điều chỉnh để phù hợp với tình huống.
Trích xuất n từ cuối cùng từ ô
Công thức này có thể được khái quát hóa để trích xuất các từ N cuối cùng từ một ô bằng cách thay thế 1 mã hóa trong ví dụ bằng [N-1]. Ngoài ra, nếu bạn đang trích xuất nhiều từ, bạn có thể muốn thay thế đối số được mã hóa cứng vào giữa, 100, với số lượng lớn hơn. Để đảm bảo cho bạn số đủ lớn, bạn có thể chỉ cần sử dụng chức năng LEN như sau:
=MID[B5,FIND["@",SUBSTITUTE[B5," ","@",LEN[B5]-LEN[SUBSTITUTE[B5," ",""]]-[N-1]]]+1,LEN[B5]]
- Lời khuyên Excel
- Chức năng Excel
- Công thức Excel
- Biểu đồ Excel
- Lời khuyên từ
- Lời khuyên Outlook
Bạn đã bao giờ chịu đựng vấn đề mà bạn cần trích xuất một từ nhất định từ chuỗi văn bản trong một bảng tính? Ví dụ: bạn có một loạt các chuỗi văn bản sau đây cần thiết để có được từ đầu tiên/cuối cùng hoặc thứ n từ họ, ở đây tôi có thể nói về một số cách hiệu quả để bạn giải quyết nó.
- Trích xuất từ đầu tiên hoặc họ từ chuỗi văn bản với các công thức
- Trích xuất từ thứ n từ chuỗi văn bản với chức năng do người dùng xác định
- Trích xuất từng từ từ chuỗi văn bản và liệt kê chúng theo chiều ngang hoặc chiều dọc
- Trích xuất từ thứ n từ chuỗi văn bản với một công cụ tuyệt vời
Trích xuất từ đầu tiên hoặc họ từ chuỗi văn bản với các công thức
Nếu bạn cần trích xuất từ đầu tiên từ danh sách các chuỗi văn bản, các công thức sau có thể giúp bạn.
Để trích xuất từ đầu tiên, vui lòng nhập công thức bên dưới vào một ô trống, sau đó kéo tay cầm tự động của ô này vào phạm vi khi bạn cần.
= If [iserr [find ["", a2]], "", trái [a2, find ["", a2] -1]]]
Để trích xuất từ cuối cùng từ mỗi ô, vui lòng áp dụng công thức này:
= If [iserr [tìm ["", a2]], "", phải [a2, len [a2] -find ["*", thay thế [a2, "" Thay thế [A2, "", ""]]]]]]]
Và bây giờ bạn sẽ thấy từ đầu tiên hoặc từ cuối cùng được trích xuất từ mỗi ô.
Ghi chú: Trong các công thức trên, A2 chỉ ra ô bạn sẽ trích xuất từ đầu tiên hoặc từ cuối cùng. In above formulas, A2 indicates the cell you will extract the first or the last word from.
Khó ghi nhớ các công thức phức tạp lâu dài? Công cụ tuyệt vời giúp bạn trích xuất từ thứ n chỉ với một vài lần nhấp!ith several clicks only!
Các công thức dài trên chỉ có thể trích xuất từ đầu tiên và từ cuối cùng, nhưng sẽ vô dụng để trích xuất từ thứ n được chỉ định, từ thứ hai, từ thứ sáu, v.v. Quá phức tạp để ghi nhớ và áp dụng. Ở đây, việc trích xuất từ thứ n trong tính năng ô của kutools cho excel được khuyến nghị, và nó sẽ giúp bạn trích xuất từ thứ n dễ dàng nhất có thể! Tính năng đầy đủ thử nghiệm miễn phí 30 ngày!Full Feature Free Trial 30-day!
Kutools cho Excel- bao gồm hơn 300 công cụ tiện dụng cho Excel. Tính năng đầy đủ dùng thử miễn phí 30 ngày, không cần thẻ tín dụng! Lấy nó ngay- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
Trích xuất từ thứ n từ chuỗi văn bản với chức năng do người dùng xác định
Nếu bạn muốn trích xuất từ thứ hai, thứ ba hoặc bất kỳ từ thứ n nào từ chuỗi văn bản, bạn có thể tạo chức năng do người dùng xác định để đối phó với nó.
1. Giữ các phím ALT + F11 để mở cửa sổ Microsoft Visual Basic cho các ứng dụng.. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Nhấp vào Chèn> Mô -đun và Dán mã sau vào cửa sổ Mô -đun.. Click Insert > Module, and paste the following code in the Module window.
Function FindWord[Source As String, Position As Integer]
'Update 20131202
Dim arr[] As String
arr = VBA.Split[Source, " "]
xCount = UBound[arr]
If xCount < 1 Or [Position - 1] > xCount Or Position < 0 Then
FindWord = ""
Else
FindWord = arr[Position - 1]
End If
End Function
3. Sau đó, lưu mã, trong ví dụ này, tôi sẽ nhận được từ thứ ba từ chuỗi, vì vậy hãy nhập công thức này = findword [a2,3] vào một ô trống B2, sau đó kéo tay cầm tự động của ô này vào phạm vi khi bạn cần. Xem ảnh chụp màn hình:. Then save the code, in this example, I will get the third word from the string, so type this formula =findword[A2,3] into a blank cell
B2, and then drag this cell's AutoFill handle to the range as you need. See screenshot:
Lưu ý: Trong công thức trên, A2 là ô mà bạn muốn trích xuất từ từ, số 3 chỉ ra từ thứ ba trong chuỗi mà bạn sẽ trích xuất và bạn có thể thay đổi chúng khi bạn cần. In the above formula, A2 is the cell where you want to extract word from, the number 3 indicates the third word in the string that you will extract, and you can change them as you need.
Trích xuất từng từ từ chuỗi văn bản và liệt kê chúng theo chiều ngang hoặc chiều dọc
Phương pháp này sẽ giới thiệu các kutools cho tiện ích phân chia của Excel để trích xuất từng từ từ các tế bào chuỗi văn bản, sau đó liệt kê các từ được trích xuất theo chiều ngang hoặc chiều dọc dựa trên nhu cầu của bạn.Split Cells utility to extract each word from text string cells, and then list extracted words horizontally or vertically based on your need.
Kutools cho Excel - bao gồm hơn 300 công cụ tiện dụng cho Excel. Tính năng đầy đủ dùng thử miễn phí 30 ngày, không cần thẻ tín dụng! Dùng thử miễn phí ngay bây giờ!- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!
1. Select the text string cells that you will extract their words from, and click the Kutools > Merge & Split > Split
Cells.
2. Trong hộp thoại mở đầu ô mở, chỉ định loại phân chia trong phần Loại, hãy chọn tùy chọn không gian trong phần Chỉ định phần Dấu phân cách và nhấp vào nút OK. Xem ảnh chụp màn hình:. In the opening Split Cells dialog box, specify the split type in the Type section, check the Space option in the Specify a separator section, and click the Ok button. See screenshot:
3. Bây giờ chỉ định phạm vi đích mà bạn sẽ xuất các từ đã trích xuất vào và nhấp vào nút OK. Now specify the destination range that you will output extracted words into, and click the OK button
Nếu bạn đã kiểm tra tùy chọn chia cho các cột trong hộp thoại phân chia trên, mỗi từ được trích xuất từ mỗi chuỗi văn bản và được liệt kê theo chiều dọc. Split to Columns option in the above Split Cells dialog, every word is extracted from every text string and listed vertically.
Nếu bạn đã kiểm tra tùy chọn phân chia thành hàng trong hộp thoại phân tách ở trên, mỗi từ được trích xuất từ mỗi chuỗi văn bản và được liệt kê theo chiều ngang.Split to Rows option in the above Split Cells dialog, every word is extracted from every text string and listed horizontally.
Trích xuất từ thứ n từ chuỗi văn bản trong Excel với một công cụ tuyệt vời
Nếu bạn đã cài đặt kutools cho Excel, bạn có thể sử dụng công thức trợ giúp công thức của nó> trích xuất từ thứ n trong tính năng ô để nhanh chóng trích xuất từ thứ n từ ô được chỉ định một cách dễ dàng.
Kutools cho Excel- bao gồm hơn 300 công cụ tiện dụng cho Excel. Tính năng đầy đủ dùng thử miễn phí 30 ngày, không cần thẻ tín dụng! Dùng thử miễn phí ngay bây giờ!- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!
1. Chọn ô bạn sẽ đặt từ được trích xuất tại và nhấp vào Kutools> Công thức trợ giúp> Công thức trợ giúp công thức để bật tính năng này.. Select the cell you will put the extracted word at, and click Kutools > Formula Helper > Formula Helper to enable this feature.
2. Trong hộp thoại Công thức Helper, vui lòng làm như sau: [1] chọn Văn bản từ danh sách thả xuống Loại Công thức; [2] Bấm để làm nổi bật trích xuất từ thứ n trong ô trong hộp Danh sách Công thức; [3] trong hộp ô, chỉ định ô mà bạn sẽ trích xuất từ từ; [4] Trong hộp thứ n, chỉ định số.. In the Formulas Helper dialog,
please do as follows:
[1] Select Text from the Formula type drop-down list;
[2] Click to highlight Extract the nth word in cell in the Choose a formula list box;
[3] In the Cell box, specify the cell that you will extract word from;
[4] In The Nth box, specify the number.
3. Nhấp vào nút OK. Nếu cần thiết, vui lòng kéo tay cầm tự động của ô công thức và sao chép công thức vào các ô khác.. Click the Ok button. If necessary, please drag the AutoFill Handle of the formula cell and copy the formula to other cells.
Bản demo: Trích xuất từng từ từ chuỗi văn bản và liệt kê chúng theo chiều ngang hoặc chiều dọc
Kutools cho Excel bao gồm hơn 300 công cụ tiện dụng cho Excel, miễn phí thử mà không giới hạn trong 30 ngày. Tải xuống và dùng thử miễn phí ngay bây giờ! includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!
Những bài viết liên quan:
- Làm thế nào để trích xuất văn bản trước/sau không gian hoặc dấu phẩy chỉ trong Excel?
- Làm thế nào để phân chia văn bản theo không gian/dấu phẩy/dấu phân cách trong Excel?
- Làm thế nào để trích xuất văn bản giữa dấu phẩy/ngoặc/dấu ngoặc đơn trong Excel?
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng năng suất của bạn lên 80%
- Tái sử dụng: nhanh chóng chèn các công thức phức tạp, biểu đồ & nbsp; và bất cứ điều gì bạn đã sử dụng trước đây; Mã hóa các ô bằng mật khẩu; Tạo danh sách gửi thư và gửi email ...: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Thanh siêu công thức [dễ dàng chỉnh sửa nhiều dòng văn bản và công thức]; Đọc bố cục [dễ dàng đọc và chỉnh sửa số lượng lớn các ô]; Dán vào phạm vi được lọc ... [easily edit multiple lines of text and formula]; Reading Layout [easily read and edit large numbers of cells]; Paste to Filtered Range...
- Hợp nhất các ô/hàng/cột mà không mất dữ liệu; Chia nội dung tế bào; Kết hợp các hàng/cột trùng lặp ... Ngăn chặn các ô trùng lặp; So sánh phạm vi ... without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Chọn các hàng trùng lặp hoặc độc đáo; Chọn các hàng trống [tất cả các ô trống]; Siêu tìm và mờ tìm trong nhiều sách bài tập; Chọn ngẫu nhiên ... Rows; Select Blank Rows [all cells are empty]; Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Sao chép chính xác nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tham chiếu đến nhiều tờ; Chèn đạn, hộp kiểm và nhiều hơn nữa ... Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Trích xuất văn bản, thêm văn bản, xóa theo vị trí, loại bỏ không gian; Tạo và in các phần phụ phân trang; Chuyển đổi giữa nội dung ô và bình luận ..., Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Siêu bộ lọc [lưu và áp dụng các sơ đồ lọc cho các bảng khác]; Sắp xếp nâng cao theo tháng/tuần/ngày, tần suất và nhiều hơn nữa; Bộ lọc đặc biệt của in đậm, nghiêng ... [save and apply filter schemes to other sheets]; Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Kết hợp sách bài tập và bảng tính; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều tờ; Batch Chuyển đổi XLS, XLSX và PDF ...; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office/Excel 2007-2019 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ các tính năng dùng thử miễn phí 30 ngày. Đảm bảo hoàn lại tiền 60 ngày.. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Tab Office mang giao diện được lập bảng đến văn phòng và làm cho công việc của bạn dễ dàng hơn nhiều
- Kích hoạt chỉnh sửa và đọc theo bảng trong Word, Excel, PowerPoint, Nhà xuất bản, Truy cập, Visio và Dự án., Publisher, Access, Visio and Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng năng suất của bạn lên 50%và giảm hàng trăm lần nhấp chuột cho bạn mỗi ngày!
Comments[38] [38]
Chưa có xếp hạng nào. Hãy là người đầu tiên xếp hạng!