Excel trích xuất văn bản sau khoảng trắng cuối cùng

Hướng dẫn này sẽ trình bày cách trích xuất văn bản trước hoặc sau một ký tự trong Excel và Google Trang tính

Trích xuất văn bản trước ký tự bằng hàm FIND và LEFT

Để trích xuất văn bản trước dấu phẩy, chúng ta có thể sử dụng hàm LEFT và FIND

Tìm chức năng

Đầu tiên, chúng ta có thể tìm vị trí của dấu phẩy bằng cách sử dụng hàm FIND rồi trừ một giá trị được trả về để lấy độ dài của Họ

=FIND[",", B3]-1

Sử dụng chức năng TRÁI

Sau đó chúng ta sử dụng hàm left để trích xuất văn bản trước vị trí được trả về bởi hàm FIND ở trên

=LEFT[B3, C3]

Kết hợp các chức năng này mang lại công thức

=LEFT[B3, FIND[",", B3]-1]

Trích xuất văn bản sau ký tự bằng các hàm FIND, LEN và RIGHT

Trong phần tiếp theo, chúng ta sẽ sử dụng Hàm FIND, LEN và RIGHT để trích xuất văn bản sau một ký tự cụ thể trong chuỗi văn bản

Hàm TÌM

Như chúng ta đã làm trong ví dụ trước, chúng ta sử dụng Hàm find để tìm vị trí của dấu phẩy và sau đó trừ một giá trị được trả về để lấy độ dài của Họ

=FIND[",", B3]-1

LEN Hàm

Sau đó, chúng tôi sử dụng Hàm LEN để lấy tổng độ dài của văn bản

=LEN[B3]

Sau đó, chúng ta có thể kết hợp các hàm FIND và hàm LEN để lấy số lượng ký tự mà chúng ta muốn trích xuất sau dấu phẩy

Ở đây chúng tôi mô tả, cách lấy một phần của chuỗi văn bản, là phần sau phiên bản cuối cùng của một số chuỗi [dấu cách, dấu gạch chéo. ]

thích ở đây

chúng ta cần bướm [vì nó nằm sau dấu gạch chéo cuối cùng]

Làm thế nào để làm nó?

Đối với chuỗi trong A2, hãy sử dụng chuỗi này

• =MID[A1;FIND["#";SUBSTITUTE[A1;"/";"#";LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]]]]+1;100]

Tại sao?

• LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]] - đếm ký tự [dấu gạch chéo]. Trong ví dụ của chúng tôi, nó là 2
• SUBSTITUTE[A1;"/";"#";LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]]] - thay thế ký tự cuối cùng bằng # hoặc bất kỳ ký hiệu nào khác không có trong
• FIND["#";SUBSTITUTE[A1;"/";"#";LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]]]]+1 - tìm vị trí của #
• MID lấy chuỗi sau vị trí này

Nhân tiện, để có được những gì TRƯỚC phiên bản cuối cùng của một thứ gì đó, hãy sử dụng một cái gì đó như thế này

• =LEFT[A1;FIND["#";SUBSTITUTE[A1;"/";"#";LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]]]]-1]

Điều này trở nên đặc biệt cần thiết khi dữ liệu được nhập từ một nguồn bên ngoài, nguồn này có thể được định dạng khác

Một trong những vấn đề khó chịu nhất với dữ liệu đã nhập là sự hiện diện của các khoảng trắng không cần thiết

Mặc dù chúng có vẻ vô hại, nhưng chúng có khả năng cản trở quá trình xử lý dữ liệu tiếp theo, dẫn đến kết quả không mong muốn

Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn 2 cách để trích xuất văn bản sau ký tự khoảng trắng trong Excel

• sử dụng Công thức
• sử dụng VBA

Chúng tôi cũng sẽ chỉ cho bạn cách sử dụng công cụ Excel nhanh để trích xuất văn bản sau mỗi ký tự khoảng trắng trong một ô nhất định

Mục lục

• Trích xuất văn bản sau ký tự khoảng trắng trong Excel
  • Phương pháp 1. Sử dụng Công thức để Trích xuất Văn bản sau Ký tự Khoảng trắng trong Excel
  • Phương pháp 2. Sử dụng VBA để trích xuất văn bản sau ký tự khoảng trắng trong Excel
• Trích xuất văn bản sau mỗi ký tự khoảng trắng trong Excel

Trích xuất văn bản sau ký tự khoảng trắng trong Excel

Dưới đây tôi có một tập dữ liệu nơi tôi có tên và ID trong cùng một ô [được phân tách bằng ký tự khoảng trắng]

Chúng tôi chỉ muốn trích xuất tên sau id nhân viên

Nói cách khác, chúng tôi muốn trích xuất văn bản xuất hiện sau khoảng trắng đầu tiên từ mỗi ô

Có hai cách để thực hiện việc này

Vậy hãy bắt đầu

Phương pháp 1. Sử dụng Công thức để Trích xuất Văn bản sau Ký tự Khoảng trắng trong Excel

Phương pháp đầu tiên là sử dụng một công thức bao gồm các hàm RIGHT, LEN và FIND

Trước tiên chúng ta hãy xem mỗi chức năng này làm gì

Chức năng QUYỀN

Hàm RIGHT trích xuất một số ký tự nhất định bắt đầu từ phía bên phải của văn bản

Dưới đây là cú pháp của hàm RIGHT

=RIGHT[text, num_of_characters]

Trong cú pháp trên

• văn bản là chuỗi mà bạn muốn trích xuất các ký tự
• num_of_characters là số ký tự mà bạn muốn trích xuất từ ​​chuỗi văn bản, bắt đầu từ bên phải

Ví dụ để lấy ra 3 ký tự cuối của chuỗi “Xin chào” bạn sẽ sử dụng hàm như sau

=RIGHT[“Hello”,3]

Kết quả. ”xin chào”

Hàm TÌM

Hàm FIND tìm chỉ mục hoặc vị trí của một ký tự hoặc chuỗi con đã cho bên trong một văn bản đã cho. Cú pháp của nó là

=FIND[search_string,text]

Trong chức năng trên

• văn bản là chuỗi văn bản mà bạn muốn tìm kiếm. Đây có thể là một tham chiếu ô hoặc một chuỗi
• search_string là ký tự hoặc chuỗi con mà bạn muốn tìm vị trí trong văn bản

Ví dụ, để tìm vị trí của chuỗi con “el” trong văn bản “Xin chào”, bạn sẽ sử dụng hàm như sau

Kết quả. 2

Ta được kết quả 2, vì xâu con “el” bắt đầu từ vị trí thứ 2 của xâu “Xin chào”

Hàm LEN

Hàm LEN chỉ đơn giản là tính số lượng ký tự trong một văn bản nhất định. Cú pháp của nó là

=LEN[text]

Ở đây, văn bản là văn bản mà chúng ta muốn biết số lượng ký tự trong đó

Kết hợp các hàm LEN, RIGHT và FIND để trích xuất văn bản sau dấu cách

Để xóa văn bản sau ký tự khoảng trắng đầu tiên, bạn cần kết hợp 3 hàm trên thành công thức sau

=LEFT[B3, C3]

Hãy áp dụng điều này vào ví dụ của chúng ta và xem kết quả chúng ta nhận được là gì

Công thức này hoạt động như thế nào?

Để hiểu cách thức hoạt động của công thức này, chúng ta cần chia nhỏ nó ra

• Đầu tiên, chúng ta sử dụng hàm FIND để tìm vị trí của ký tự khoảng trắng trong văn bản. TÌM[“ “,A2]
• Chúng tôi muốn đếm số ký tự sau [và không bao gồm] ký tự khoảng trắng. Vì vậy, chúng tôi đã trừ vị trí của ký tự khoảng trắng đầu tiên được tìm thấy khỏi tổng độ dài của văn bản. LEN[A2]- TÌM[“ ”, A2]
• Cuối cùng, chúng tôi đã sử dụng hàm RIGHT để trích xuất các ký tự LEN[A2]- FIND[“ ”, A2] theo sau ký tự khoảng trắng. PHẢI[A2,LEN[A2]-TÌM[” “,A2]]

Do đó, chúng tôi nhận được tất cả các ký tự trong A2 xuất hiện sau ký tự khoảng trắng đầu tiên

Trong trường hợp dữ liệu của bạn có nhiều hơn một khoảng trắng liên tiếp, bạn có thể giải quyết chúng hoàn toàn bằng cách bọc hàm TRIM xung quanh công thức trên, như sau

=LEFT[B3, C3]

Phương pháp 2. Sử dụng VBA để trích xuất văn bản sau ký tự khoảng trắng trong Excel

Một cách khác để trích xuất nhanh văn bản sau ký tự khoảng trắng là sử dụng mã VBA đơn giản

Đây là mã chúng tôi sẽ sử dụng. Bạn có thể chọn và sao chép nó

=LEFT[B3, C3]

Thực hiện theo các bước sau

• Nhấp vào tab Nhà phát triển trong dải băng
• Nhấp vào tùy chọn Visual Basic. Điều này sẽ mở cửa sổ VB Editor
• Nhấp vào tùy chọn Chèn trong menu rồi nhấp vào Mô-đun. Điều này sẽ thêm một mô-đun mới nơi bạn có thể chèn mã VBA
• Sao chép-dán các dòng mã trên và dán vào cửa sổ mã mô-đun

Bây giờ bạn đã có mã VBA trong tệp, dưới đây là các bước bạn có thể sử dụng để chạy mã

• Chọn phạm vi ô chứa văn bản mà bạn muốn trích xuất các ký tự sau ký tự khoảng trắng. Bạn cần đảm bảo cột liền kề trống, vì đây là nơi macro sẽ hiển thị kết quả
• Nhấp vào tab Nhà phát triển
• Nhấp vào Macro
• Trong hộp thoại Macro mở ra, chọn extract_text_after_space
• Nhấp vào Chạy

Bây giờ, bạn sẽ thấy văn bản được trích xuất bên cạnh phạm vi ô đã chọn của mình

Ghi chú. Đoạn mã trên hiển thị văn bản được trích xuất trong cột ngay bên cạnh, vì vậy hãy đảm bảo rằng cột ở bên phải văn bản gốc của bạn trống. Ngoài ra, hãy nhớ rằng những thay đổi do mã VB thực hiện là không thể đảo ngược, vì vậy hãy đảm bảo bạn có bản sao lưu dữ liệu

Trích xuất văn bản sau mỗi ký tự khoảng trắng trong Excel

Bây giờ chúng ta hãy xem trường hợp bạn có thể muốn trích xuất văn bản sau mỗi ký tự khoảng trắng trong một ô nhất định, thay vì chỉ ký tự khoảng trắng đầu tiên

Ví dụ: giả sử bạn có chuỗi “IKS103 John Smith”

Bạn có thể muốn trích xuất từng từ theo sau khoảng trắng trong một cột riêng biệt, như hình bên dưới

Đối với điều này, đây là một thủ thuật thực sự dễ dàng và nhanh chóng

Excel cung cấp một công cụ tiện dụng được gọi là tính năng 'Chuyển văn bản thành cột'

Tính năng này cho phép bạn tách văn bản thành các cột khác nhau dựa trên dấu phân cách nhất định. Ví dụ: trong tập dữ liệu mẫu của chúng tôi, mỗi từ được phân cách bằng dấu cách

Để trích xuất văn bản sau mỗi khoảng trắng bằng tính năng Chuyển văn bản thành cột, đây là các bước bạn cần thực hiện

1. Chọn cột có ký tự khoảng trắng mà sau đó bạn muốn tách văn bản
2. Nhấp vào tab Dữ liệu và sau đó chọn tùy chọn “Chuyển văn bản thành cột” [nằm trong nhóm Công cụ dữ liệu]

1. Thao tác này sẽ mở hộp thoại 'Chuyển văn bản thành cột'

1. Ở Bước 1, hãy đảm bảo chọn nút radio bên cạnh tùy chọn 'Delimited'

1. Nhấn tiếp
2. Ở Bước 2, đảm bảo rằng chỉ có hộp kiểm bên cạnh tùy chọn Dấu cách được chọn

1. Ngoài ra còn có một hộp kiểm cho phép bạn chỉ định nếu bạn muốn 'Coi các dấu phân cách liên tiếp là một'. Điều này có nghĩa là nếu bạn có nhiều ký tự khoảng cách liên tiếp, bạn muốn Excel cắt tất cả chúng thành một. Bỏ chọn tùy chọn này nếu bạn cần

1. Trong phần Xem trước dữ liệu, bạn sẽ có thể xem đầu ra cuối cùng trông như thế nào

1. Nhấn tiếp
2. Ở Bước 3, có một tùy chọn để chỉ định định dạng của dữ liệu được trích xuất. Theo mặc định, tùy chọn Chung sẽ được chọn – điều này đảm bảo rằng các cột đầu ra có cùng định dạng với các ô ban đầu. Bạn có thể để tùy chọn Chung mặc định được chọn

1. Trong trường nhập 'Đích', hãy nhập tham chiếu đến ô mà bạn muốn bắt đầu chuỗi phân tách đầu tiên. Chúng tôi muốn bắt đầu từ ô B2, vì vậy hãy nhập $B$2 vào trường nhập liệu

1. Nhấp vào Kết thúc

Bây giờ bạn có thể thấy rằng văn bản của mỗi ô đã được tách thành các cột khác nhau

Nếu bạn không muốn giữ cột đầu tiên [ID], thì bạn có thể tiếp tục và xóa toàn bộ cột bằng cách chọn tiêu đề cột, nhấp chuột phải và chọn “Xóa”

Đây là cái nhìn cuối cùng

Trong hướng dẫn này, chúng tôi đã chỉ cho bạn hai cách để trích xuất văn bản sau ký tự khoảng trắng trong Excel

Phương pháp đầu tiên liên quan đến việc sử dụng công thức, trong khi phương pháp thứ hai liên quan đến một số tập lệnh VBA

Chúng tôi cũng đã chỉ cho bạn cách bạn có thể trích xuất văn bản sau mỗi khoảng trắng trong một ô nhất định bằng cách sử dụng tính năng Chuyển văn bản thành cột của Excel