Văn bản nhất định từ chuỗi, URL từ liên kết, v.v.

Phần cuối cùng trong các thao tác 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 cách trích xuất nhiều dữ liệu khác nhau – văn bản, ký tự, số, URL, địa chỉ email, ngày và giờ, v.v. – từ các vị trí khác nhau trong nhiều ô Google Trang tính cùng một lúc.

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

Công thức trong Google Trang tính là tất cả mọi thứ. Trong khi một số combo thêm văn bản và số và loại bỏ các ký tự khác nhau, một số người trong số họ 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í: ký tự N đầu tiên / cuối cùng / giữa

Các chức năng dễ xử lý nhất khi bạn sắp lấy dữ liệu từ các ô của Google Trang tính là LEFT, RIGHT và MID. Họ nhận được bất kỳ dữ liệu nào theo vị trí.

Trích xuất dữ liệu từ đầu ô trong Google Trang tính

Bạn có thể dễ dàng kéo ra N ký tự đầu tiên bằng cách sử dụng hàm LEFT:

LEFT (chuỗi,[number_of_characters])

• chuỗi là văn bản mà bạn muốn trích xuất dữ liệu.
• number_of_characters là số ký tự cần lấy ra 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 có 6 ký hiệu ở đầu ô, vì vậy công thức bạn cần là:

=LEFT(A2,6)

Tiền boa. ArrayFormula sẽ giúp bạn có thể nhận được 6 ký tự từ toàn bộ dải ô cùng một lúc:

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

Trích xuất dữ liệu từ cuối các ô trong Google Trang tính

Để kéo ra N ký tự cuối cùng từ các ô, hãy sử dụng hàm RIGHT để thay thế:

RIGHT (chuỗi,[number_of_characters])

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

Hãy lấy tên quốc gia đó từ các số điện thoại giống nhau:

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

=RIGHT(A2,2)

Tiền boa. ArrayFormula cũng sẽ giúp bạn trích xuất dữ liệu từ cuối tất cả các ô Google Trang tính cùng một lúc:

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

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

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

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

MID (chuỗi, start_at, extract_length)

• chuỗi – văn bản mà bạn muốn lấy phần giữa ra khỏi đó.
• bắt đầu tại – vị trí của ký tự mà bạn muốn bắt đầu lấy dữ liệu.
• extract_length – số ký tự bạn cần kéo ra.

Bằng ví dụ về các số điện thoại giống nhau, hãy tự tìm các số điện thoại không có mã quốc gia và tên viết tắt quốc gia của chúng:

Vì mã quốc gia kết thúc bằng ký tự thứ 6 và thứ 7 là dấu gạch ngang, tôi sẽ kéo các 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)

Tiền boa. 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 từng ô cùng một lúc:

=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ư hình trên) không phải là một tùy chọn. Các chuỗi bắt buộc có thể nằm trong bất kỳ phần nào của ô của bạn và bao gồm một số 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 Trang tính sẽ không phải là Google Trang tính 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ừ chuỗi.

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

Trích xuất dữ liệu trước một văn bản nhất định – TRÁI + TÌM KIẾM

Bất cứ khi nào bạn muốn trích xuất dữ liệu đứng trước một văn bản nhất định, hãy sử dụng 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ên trái của chúng)
• TÌM KIẾM tìm kiếm các ký tự / chuỗi nhất định và nhận vị trí của chúng.

Kết hợp các ký tự này – và LEFT sẽ trả về số ký tự được đề xuất bởi 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 những 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 ‘ea’, tôi cần phải trừ đi 1 từ vị trí đó. Nếu không, ‘e’ cũng sẽ được trả về. Vì vậy, cuối cùng tôi nhận được 9.
3. TRÁI nhìn vào A2 và nhận được 9 ký tự đầu tiên.

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

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

REGEXREPLACE (văn bản, biểu_thức_chính_phục, thay thế)

• bản văn là một chuỗi hoặc một ô mà bạn muốn thực hiện thay đổi
• biểu hiện thông thường là sự kết hợp của các ký tự đại diện cho một phần của văn bản mà bạn đang tìm kiếm
• thay thế là bất cứ thứ gì bạn muốn lấy thay vì cái đó bản vă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 hoạt động chính xác của công thức này:

1. A2 là một ô mà tôi đang trích xuất dữ liệu.
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 ký 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ạ chính nó được đặt trong dấu ngoặc kép.
3. “2 đô la” là những gì tôi muốn lấy – nhóm thứ hai (do đó là số 2) từ đối số trước.

Tiền boa. Tất cả các ký tự được sử dụng trong biểu thức chính quy được thu thập trên này trang đặc biệt.

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

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

Mặt nạ (hay còn gọi là biểu thức chính quy) cũng sẽ hữu ích. Trong thực tế, tôi sẽ lấy cùng một chức năng REGEXREPLACE và thay đổi biểu thức chính quy:

=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 nhận mọi thứ trừ các chữ số. Biểu tượng ^ dấu mũ đó là thứ tạo ra 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 “nothing”. Hay 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 các 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 bảng chữ cái từ các ô của Google Trang tính. Sự co lại cho biểu thức chính quy viết tắt của văn bản được gọi là tương ứng – alpha:

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

Công thức này lấy mọi thứ trừ các chữ cái (AZ, az) và thay thế nó bằng “nothing” theo nghĩa đen. Hay nói một cách khác, chỉ lấy ra các chữ cái.

Các cách không cần công thức để trích xuất dữ liệu từ các ô Google Trang tính

Nếu bạn đang tìm kiếm một cách dễ dàng không cần công thức để trích xuất nhiều loại dữ liệu khác nhau, bạn đã đến đúng nơi. Của chúng tôi Dụng cụ điện tiện ích bổ sung 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ác tiện ích bổ sung Power Tools

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 đang 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 Trang tính.

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ụ 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 có biểu thức chính quy.

Nhớ lại điểm thứ hai của bài viết này với REGEXREPLACE và biểu thức chính quy? Đây là cách đơn giản cho tiện ích bổ sung:

Tùy chọn bổ sung

Như bạn thấy, có một số tùy chọn bổ sung (chỉ các hộp kiểm) mà bạn có thể nhanh chóng bật / tắt để có được kết quả chính xác nhất:

1. Chỉ lấy các chuỗi của trường hợp văn bản được yêu cầu.
2. Kéo tất cả các lần xuất hiện khỏi 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ả vào bên phải của dữ liệu nguồn.
4. Xóa văn bản đã trích xuất khỏi dữ liệu nguồn.

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

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

1. Các số cùng với số thập phân của chúng giữ nguyên dấu phân cách thập phân / hàng nghìn:
   
2. N ký tự bắt đầu từ một vị trí nhất định trong một ô.
3. Siêu liên kết (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. Chiết xuất bằng mặt nạ và các ký tự đại diện của nó – * và ? – thực hiện thủ thuật:

• Ví dụ: bạn có thể hiển thị mọi thứ giữa các dấu ngoặc bằng cách sử dụng mặt nạ sau:
• Hoặc nhận những SKU chỉ có 5 số trong id của chúng:
• Mã hàng ????? Hoặc, như tôi hiển thị trên ảnh chụp màn hình bên dưới, hãy kéo mọi thứ sau mỗi ‘ea’ trong mỗi ô:

Trích xuất dữ liệu trong Google Trang tính theo mặt nạ.

Trích xuất ngày và giờ 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à giờ từ các dấu thời gian – nó được gọi làChia ngày và giờ

.

Tiện ích bổ sung Chia Ngày & Giờ. 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 giờ – từ dấu thời gian trong Google Trang tính và nhấnTách ra

Trích xuất ngày từ dấu thời gian trong Google Trang tính. Công cụ này cũng là một phần của Tiện ích bổ sung Power Tools
vì vậy, khi bạn cài đặt nó để lấy bất kỳ dữ liệu nào từ các ô của Google Trang tính, nó sẽ giúp bạn hoàn toàn.

 

The post Văn bản nhất định từ chuỗi, URL từ liên kết, v.v. appeared first on Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng | KetoanMVB.

https://ift.tt/3eIS8QR Bá Teppi https://ift.tt/3iCHoUX July 22, 2021 at 09:47AM