Hướng dẫn how do i extract specific text from google sheets? - làm cách nào để trích xuất văn bản cụ thể từ trang tính google?

Phần tiếp theo này của các hoạt động của chúng tôi với văn bản trong bảng tính được dành cho việc trích xuất. Tìm ra các cách để trích xuất dữ liệu khác nhau - văn bản, ký tự, số, URL, địa chỉ email, ngày & giờ, v.v. - từ các vị trí khác nhau trong nhiều ô Google Sheets cùng một lúc.

Công thức Google Sheets để trích xuất văn bản và số từ chuỗi

Công thức trong Google Sheets là tất cả. Trong khi một số combo thêm văn bản & số và xóa các ký tự khác nhau, một số trong số chúng cũng trích xuất văn bản, số, ký tự riêng biệt, v.v.

Trích xuất dữ liệu theo vị trí: Đầu tiên/cuối/giữa n chars

Các chức năng dễ nhất để giải quyết khi bạn sắp lấy dữ liệu từ các ô Google Sheets bị bỏ lại, phải và giữa. Họ nhận được bất kỳ dữ liệu theo vị trí.

Trích xuất dữ liệu từ đầu các ô trong Google Sheets

Bạn có thể dễ dàng rút ra n ký tự đầu tiên bằng hàm bên trái:

LEFT(string,[number_of_characters])

• Chuỗi là văn bản mà bạn muốn trích xuất dữ liệu từ.
• Number_of_Characters là số lượng ký tự để bắt đầu từ bên trái.

Đây là ví dụ đơn giản nhất: Hãy lấy mã quốc gia từ các số điện thoại:

Như bạn có thể thấy, mã quốc gia lấy 6 ký hiệu ở đầu các ô, vì vậy công thức bạn cần là:

=LEFT(A2,6)

Mẹo. ArrayFormula sẽ có thể nhận được 6 ký tự từ toàn bộ phạm vi cùng một lúc: ArrayFormula will make it possible to get 6 characters from the entire range at once:

=ArrayFormula(LEFT(A2:A7,6))

Trích xuất dữ liệu từ phần cuối của các ô trong Google Sheets

Để rút ra n ký tự cuối cùng từ các ô, thay vào đó, hãy sử dụng đúng chức năng: thay vào đó

RIGHT(string,[number_of_characters])

• Chuỗi vẫn là văn bản (hoặc tham chiếu ô) để trích xuất dữ liệu từ.
• Number_of_Characters cũng là số lượng ký tự để lấy từ bên phải.

Hãy lấy tên quốc gia đó từ cùng một số điện thoại:

Họ chỉ mất 2 ký tự và đó chính xác là những gì tôi đề cập trong công thức:

=RIGHT(A2,2)

Mẹo. ArrayFormula cũng sẽ giúp bạn trích xuất dữ liệu từ cuối tất cả các ô Google Sheets cùng một lúc: ArrayFormula will also help you extract data from the end of all Google Sheets cells at once:

=ArrayFormula(RIGHT(A2:A7,2))

Trích xuất dữ liệu từ giữa các ô trong Google Sheets

Nếu có các chức năng trích xuất dữ liệu từ đầu và phần cuối của các ô, cũng phải có một chức năng để trích xuất dữ liệu từ giữa. Và vâng - có một.

Nó được gọi là mid:

Mid (String, beTAON_AT, extract_length)

• Chuỗi - văn bản mà bạn muốn lấy phần giữa từ.
• BEFFORD_AT - Vị trí của nhân vật mà bạn muốn bắt đầu nhận dữ liệu.
• Extract_length - Số lượng ký tự bạn cần rút ra.

Theo ví dụ về cùng một số điện thoại, chúng ta hãy tự tìm số điện thoại mà không có mã quốc gia và viết tắt quốc gia của họ:

Khi mã quốc gia kết thúc với ký tự thứ 6 và thứ 7 là dấu gạch ngang, tôi sẽ kéo số bắt đầu từ chữ số thứ 8. Và tôi sẽ nhận được tổng cộng 8 chữ số:

=MID(A2,8,8)

Mẹo. Thay đổi một ô thành toàn bộ phạm vi và gói nó trong ArrayFormula sẽ cung cấp cho bạn kết quả cho mỗi ô cùng một lúc: Changing one cell to the entire range and wrapping it in ArrayFormula will provide you with the result for each cell at once:

=ArrayFormula(MID(A2:A7,8,8))

Trích xuất văn bản/số từ chuỗi

Đôi khi trích xuất văn bản theo vị trí (như hiển thị ở trên) không phải là một tùy chọn. Các chuỗi cần thiết có thể nằm trong bất kỳ phần nào trong các ô của bạn và bao gồm một số lượng ký tự khác nhau buộc bạn phải tạo các công thức khác nhau cho mỗi ô.

Nhưng Google Sheets sẽ không phải là Google Sheets nếu nó không có các chức năng khác giúp trích xuất văn bản từ các chuỗi.

Hãy xem xét một vài cách có thể cung cấp bảng tính.

Trích xuất dữ liệu trước một văn bản nhất định - còn lại+tìm kiếm

Bất cứ khi nào bạn muốn trích xuất dữ liệu trước một văn bản nhất định, hãy sử dụng bên trái + Tìm kiếm:

• Trái được sử dụng để trả về một số ký tự nhất định từ đầu tế bào (từ bên trái của chúng)
• Tìm kiếm tìm kiếm một số ký tự/chuỗi nhất định và có được vị trí của họ.

Kết hợp những điều này - và bên trái sẽ trả về số lượng ký tự được đề xuất bằng tìm kiếm.

Đây là một ví dụ: Làm thế nào để bạn trích xuất mã văn bản trước mỗi 'EA'?

Đây là công thức sẽ giúp bạn trong các trường hợp tương tự:

=LEFT(A2,SEARCH("ea",A2)-1)

Đây là những gì xảy ra trong công thức:

1. Tìm kiếm ("EA", A2) tìm kiếm 'EA' trong A2 và trả về vị trí mà 'EA' bắt đầu cho mỗi ô - 10.
2. Vì vậy, vị trí thứ 10 là nơi 'E' cư trú. Nhưng vì tôi muốn mọi thứ ngay trước khi 'EA', tôi cần trừ 1 từ vị trí đó. Nếu không, 'E' cũng sẽ được trả lại. Vì vậy, cuối cùng tôi nhận được 9.
3. Còn lại nhìn A2 và nhận được 9 ký tự đầu tiên.

Trích xuất dữ liệu sau văn bản

Cũng có phương tiện để có được mọi thứ sau một chuỗi văn bản nhất định. Nhưng lần này, đúng sẽ không giúp được gì. Thay vào đó, Regexreplace lần lượt.

Mẹo. Regexreplace sử dụng các biểu thức thường xuyên. Nếu bạn chưa sẵn sàng đối phó với họ, có một giải pháp dễ dàng hơn nhiều được mô tả dưới đây. REGEXREPLACE uses regular expressions. If you're not ready to deal with them, there's a much easier solution described below.

Regexreplace (văn bản, thường xuyên_expression, thay thế)

• Văn bản là một chuỗi hoặc một ô mà bạn muốn thay đổi
• thường xuyên_expression là sự kết hợp của các ký tự là viết tắt của một phần của văn bản mà bạn đang tìm kiếm
• Thay thế là bất cứ điều gì bạn muốn có được thay vì văn bản đó

Vì vậy, làm thế nào để bạn sử dụng nó để trích xuất dữ liệu sau một văn bản nhất định - 'ea' trong ví dụ của tôi?

Dễ dàng - Sử dụng công thức này:

=REGEXREPLACE(A2,"(.*)ea(.*)","$2")

Hãy để tôi giải thích cách thức công thức này hoạt động chính xác:

1. A2 là một ô tôi đang trích xuất dữ liệu từ.
2. "(.*) ea (.*)" là biểu thức chính quy của tôi (hoặc bạn có thể gọi nó là mặt nạ). Tôi tìm kiếm 'EA' và đặt tất cả các nhân vật khác vào dấu ngoặc. Có 2 nhóm ký tự - mọi thứ trước 'EA' là nhóm đầu tiên (.*) Và mọi thứ sau 'ea' là nhóm thứ hai (.*). Toàn bộ mặt nạ được đặt vào các trích dẫn kép.
3. "$ 2" là những gì tôi muốn nhận được - nhóm thứ hai (do đó số 2) từ đối số trước đó.

Mẹo. Tất cả các ký tự được sử dụng trong các biểu thức thông thường được thu thập trên trang đặc biệt này. All characters used in regular expressions are collected on this special page.

Trích xuất số từ các ô Google Sheets

Điều gì sẽ xảy ra nếu bạn muốn trích xuất các số khi vị trí của họ và bất cứ điều gì đi trước và sau không quan trọng?

Mặt nạ (a.k.a. Biểu thức thông thường) cũng sẽ giúp đỡ. Trên thực tế, tôi sẽ có chức năng Regexreplace tương tự và thay đổi biểu thức thông thường:

=REGEXREPLACE(A2,"[^[:digit:]]", "")

1. A2 là một ô mà tôi muốn lấy những con số đó.
2. "[^[: Digit:]]" là một biểu thức chính quy lấy mọi thứ trừ các chữ số. Biểu tượng ^Caret đó là những gì tạo nên một ngoại lệ cho các chữ số.
3. "" Thay thế mọi thứ ngoại trừ các ký tự số bằng "không có gì". Hoặc, nói cách khác, loại bỏ nó hoàn toàn, chỉ để lại số trong các ô. Hoặc, trích xuất số :)

Trích xuất văn bản bỏ qua số và các ký tự khác

Theo cách tương tự, bạn chỉ có thể lấy dữ liệu chữ cái từ các tế bào Google Sheets. Sự co lại cho biểu thức chính quy là viết tắt của văn bản được gọi là phù hợp - alpha:

=REGEXREPLACE(A2,"[^[:alpha:]]", "")

Công thức này lấy tất cả mọi thứ nhưng các chữ cái (A-Z, A-Z) và thay thế nó bằng "không có gì". Hoặc, để đặt nó theo một cách khác, chỉ lấy ra các chữ cái.

Các cách không có công thức để trích xuất dữ liệu từ các ô Google Sheets

Nếu bạn đang tìm kiếm một cách dễ dàng không có công thức để trích xuất nhiều loại dữ liệu khác nhau, bạn đã đến đúng nơi. Tiện ích bổ sung công cụ năng lượng của chúng tôi chỉ có các công cụ cho công việc.

Trích xuất các loại dữ liệu khác nhau bằng cách sử dụng các tiện ích bổ sung công cụ điện

Công cụ đầu tiên tôi muốn bạn biết được gọi là trích xuất. Nó thực hiện chính xác những gì bạn đã tìm kiếm trong bài viết này - trích xuất các loại dữ liệu khác nhau từ các ô Google Sheets.

Cài đặt thân thiện với người dùng

Tất cả các trường hợp tôi đã đề cập ở trên không chỉ có thể giải quyết được với tiện ích bổ sung. Công cụ này thân thiện với người dùng, vì vậy tất cả những gì bạn cần làm là chọn phạm vi bạn muốn xử lý và đánh dấu vào các hộp kiểm bắt buộc. Không có công thức, không biểu hiện thông thường.The tool is user-friendly so all you need to do is select the range you want to process and tick off the required checkboxes. No formulas, no regular expressions.

Bạn có nhớ điểm thứ hai của bài viết này với Regexreplace và các biểu thức thông thường không? Đây là cách đơn giản cho tiện ích bổ sung:

Extra-options

Như bạn có thể thấy, có một số tùy chọn bổ sung (chỉ cần kiểm tra) mà bạn có thể nhanh chóng bật/tắt để có được kết quả chính xác nhất:extra options (just checkboxes) that you can quickly turn on/off to get the most precise result:

1. Chỉ nhận các chuỗi của trường hợp văn bản cần thiết.
2. Kéo ra tất cả các lần xuất hiện từ mỗi ô và đặt chúng vào một ô hoặc các cột riêng biệt.
3. Chèn một cột mới với kết quả ở bên phải của dữ liệu nguồn.
4. Xóa văn bản trích xuất từ ​​dữ liệu nguồn.

Trích xuất các loại dữ liệu khác nhau

Không chỉ các công cụ năng lượng trích xuất dữ liệu trước/sau/giữa các chuỗi văn bản nhất định và các ký tự đầu tiên/cuối cùng; Nhưng nó cũng rút ra những điều sau đây:

1. Các con số cùng với số thập phân của chúng giữ nguyên các dấu phân cách thập phân/hàng ngàn người:
   
2. N ký tự bắt đầu từ một vị trí nhất định trong một ô.
3. HyperLinks (văn bản + liên kết), URL (liên kết), địa chỉ email.

Trích xuất bất kỳ chuỗi dữ liệu nào từ mọi nơi

Ngoài ra còn có một tùy chọn để thiết lập mẫu chính xác của riêng bạn và sử dụng nó để trích xuất. Trích xuất bằng mặt nạ và ký tự ký tự đại diện của nó - * và? - Làm thủ thuật:

• Ví dụ: bạn có thể đưa ra mọi thứ giữa các dấu ngoặc bằng mặt nạ sau: (*)
• Hoặc nhận được những SKU chỉ có 5 số trong ID của họ: SKU ?????
• Hoặc, như tôi hiển thị trên ảnh chụp màn hình bên dưới, kéo mọi thứ sau mỗi 'ea' trong mỗi ô: ea*

Trích xuất ngày và thời gian từ dấu thời gian

Như một phần thưởng, có một công cụ nhỏ hơn sẽ trích xuất ngày và thời gian từ dấu thời gian - nó được gọi là ngày và giờ chia.

Mặc dù nó được tạo ra để phân chia dấu thời gian ngay từ đầu, nhưng nó hoàn toàn có khả năng nhận được một trong những đơn vị mong muốn riêng lẻ:

Chỉ cần chọn một trong các hộp kiểm tùy thuộc vào những gì bạn muốn trích xuất - ngày hoặc thời gian - từ dấu thời gian trong Google Sheets và Hit Split. Đơn vị cần thiết sẽ được sao chép vào một cột mới (hoặc nó sẽ thay thế dữ liệu gốc nếu bạn cũng chọn hộp kiểm cuối cùng):Split. The required unit will be copied over to a new column (or it will replace the original data if you select the last checkbox as well):

Công cụ này cũng là một phần của tiện ích bổ sung công cụ điện, vì vậy một khi bạn cài đặt nó để nhận bất kỳ dữ liệu nào từ các ô Google Sheets, nó sẽ giúp bạn hoàn toàn bảo hiểm. Nếu không, xin vui lòng để lại nhận xét và chúng tôi sẽ giúp bạn ra ngoài :)

Bạn cũng có thể quan tâm