Làm cách nào để tách văn bản trong một ô trong excel?

dòng dưới cùng. Học cách sử dụng công thức và hàm trong Excel để chia họ và tên thành cột họ và tên

Cấp độ kỹ năng. Trung gian

Xem hướng dẫn

Tải xuống tệp Excel

Bạn có thể tải cả file before và after bên dưới. Tệp trước là để bạn có thể làm theo và tệp sau bao gồm tất cả các công thức đã được viết

Tách văn bản thành các cột riêng biệt

Chúng ta đã nói về nhiều cách khác nhau để lấy văn bản trong một cột và chia nó thành hai. Cụ thể, chúng tôi đã xem xét ví dụ phổ biến về việc lấy cột Họ và tên và tách nó thành Tên và Họ

Giải pháp đầu tiên chúng tôi xem xét đã sử dụng Power Query và bạn có thể xem hướng dẫn đó tại đây. Cách tách ô và văn bản trong Excel bằng Power Query. Sau đó, chúng tôi đã khám phá cách sử dụng tính năng Văn bản thành Cột được tích hợp trong Excel. Tách ô có văn bản thành cột trong Excel. Hôm nay, tôi muốn chỉ cho bạn cách thực hiện điều tương tự với các công thức

Sử dụng 4 hàm để xây dựng công thức của chúng tôi

Để tách cột Họ và Tên của chúng ta thành Họ và Tên bằng công thức, chúng ta cần sử dụng bốn hàm khác nhau. Chúng tôi sẽ sử dụng TÌM KIẾM và TRÁI để lấy tên đầu tiên. Sau đó, chúng tôi sẽ sử dụng LEN và RIGHT để lấy họ

Chức năng TÌM KIẾM

Chìa khóa để tách biệt tên và họ là để Excel xác định điểm chung của tất cả các tên đầy đủ. Yếu tố chung đó là ký tự khoảng trắng ngăn cách hai tên. Để giúp công thức của chúng tôi xác định mọi thứ ở bên trái của ký tự khoảng trắng đó là tên, chúng tôi cần sử dụng hàm TÌM KIẾM

Hàm TÌM KIẾM trả về số ký tự tại đó một ký tự hoặc chuỗi văn bản cụ thể được tìm thấy, đọc từ trái sang phải. Nói cách khác, ký tự khoảng trắng trong dòng ký tự tạo nên tên đầy đủ là số nào?

Có ba đối số cho TÌM KIẾM

• Đối số đầu tiên cho hàm TÌM KIẾM là find_text. Văn bản chúng tôi muốn tìm trong các mục nhập của mình là ký tự khoảng trắng. Vì vậy, đối với find_text, chúng tôi nhập ” “, đảm bảo bao gồm dấu ngoặc kép
• Đối số thứ hai là within_text. Đây là văn bản chúng tôi đang tìm kiếm ký tự khoảng trắng. Đó sẽ là ô có tên đầy đủ. Trong ví dụ của chúng tôi, ô đầu tiên có tên đầy đủ là A2. Vì chúng tôi đang làm việc với Bảng Excel, công thức sẽ sao chép xuống và thay đổi thành B2, C2, v.v. , cho mỗi hàng tương ứng
• Đối số thứ ba và cuối cùng là [start_num]. Đối số này dành cho trường hợp bạn muốn bỏ qua một số ký tự nhất định trong văn bản trước khi bắt đầu tìm kiếm. Trong trường hợp của chúng tôi, chúng tôi muốn tìm kiếm toàn bộ văn bản, bắt đầu bằng ký tự đầu tiên, vì vậy chúng tôi không cần xác định đối số này

Tất cả cùng nhau, công thức của chúng tôi đọc. =TÌM KIẾM(” “,A2)

Tôi đã bắt đầu với hàm TÌM KIẾM vì nó sẽ được sử dụng làm một trong các đối số cho hàm tiếp theo mà chúng ta sẽ xem xét. Đó là hàm LEFT,

Hàm LEFT

Hàm LEFT trả về số ký tự đã chỉ định từ đầu chuỗi văn bản. Để chỉ định số đó, chúng tôi sẽ sử dụng giá trị mà chúng tôi vừa xác định bằng chức năng TÌM KIẾM. Hàm LEFT sẽ kéo ra các chữ cái từ bên trái của cột Họ và Tên

Hàm LEFT có hai đối số

• Đối số đầu tiên là văn bản. Đó chỉ là ô mà hàm đang lấy từ đó—trong trường hợp của chúng ta là A2
• Đối số thứ hai là [num_chars]. Đây là số ký tự mà hàm sẽ kéo. Đối với đối số này, chúng tôi sẽ sử dụng công thức mà chúng tôi đã tạo ở trên và trừ 1 khỏi công thức đó, vì chúng tôi thực sự không muốn bao gồm ký tự khoảng trắng trong kết quả của mình. Vì vậy, đối với ví dụ của chúng tôi, đối số này sẽ là TÌM KIẾM(” “,A2)-1

Tất cả cùng nhau, công thức của chúng ta là =LEFT(A2,SEARCH(” “,A2)-1)

Bây giờ chúng ta đã trích xuất tên bằng hàm LEFT, bạn có thể đoán cách chúng ta sẽ sử dụng hàm RIGHT. Nó sẽ kéo ra tên cuối cùng. Nhưng trước khi chúng ta đến đó, hãy để tôi giải thích một trong những thành phần mà chúng ta sẽ cần cho công thức đó. Đó là hàm LEN

Hàm LEN

LEN là viết tắt của CHIỀU DÀI. Hàm này trả về số ký tự trong một chuỗi văn bản. Trong tên của tôi có 12 ký tự. 3 cho Jon, 8 cho Acampora và 1 cho khoảng trống ở giữa

Chỉ có một đối số cho LEN và đó là xác định văn bản nào sẽ đếm các ký tự từ. Ví dụ của chúng tôi, một lần nữa chúng tôi đang sử dụng A2 cho Tên đầy đủ. Công thức của chúng tôi chỉ đơn giản là =LEN(A2)

Chức năng QUYỀN

Công thức RIGHT trả về số lượng ký tự được chỉ định từ cuối chuỗi văn bản. RIGHT có hai đối số

• Đối số đầu tiên là văn bản. Đây là văn bản mà nó đang xem qua để trả về đúng ký tự. Cũng giống như hàm LEFT ở trên, chúng ta đang xem ô A2
• Đối số thứ hai là [num_chars]. Đối với đối số này, chúng tôi muốn trừ số ký tự mà chúng tôi đã xác định bằng hàm TÌM KIẾM khỏi tổng số ký tự mà chúng tôi đã xác định bằng hàm LEN. Điều đó sẽ cho chúng ta số ký tự trong họ

Công thức của chúng ta, tất cả cùng nhau, là =RIGHT(A2,LEN(A2)-SEARCH(” “,A2))

Lưu ý rằng chúng tôi đã không trừ 1 như chúng tôi đã làm trước đây, vì chúng tôi muốn ký tự khoảng trắng được bao gồm trong số đang bị trừ khỏi tổng độ dài

Ưu và nhược điểm của việc sử dụng công thức để chia ô

Một ưu điểm nổi bật của phương pháp tách văn bản này là cập nhật tự động. Khi chỉnh sửa, thêm hoặc xóa cột Họ và tên thì họ và tên cũng thay đổi theo. Đây là một lợi ích lớn so với việc sử dụng Văn bản thành Cột, yêu cầu bạn phải lặp lại hoàn toàn quy trình khi thực hiện các thay đổi. Và ngay cả phương pháp Power Query, mặc dù cập nhật đơn giản hơn nhiều so với Văn bản thành Cột, nhưng vẫn yêu cầu làm mới để được chọn thủ công

Tất nhiên, một nhược điểm rõ ràng của kỹ thuật này là mặc dù mỗi hàm trong số bốn hàm đều tương đối đơn giản để hiểu, nhưng cần phải suy nghĩ và dành thời gian để kết hợp tất cả chúng và tạo ra các công thức hoạt động chính xác. Nói cách khác, không phải là giải pháp dễ thực hiện nhất trong ba phương pháp được trình bày cho đến nay

Một nhược điểm khác cần xem xét là điều này chỉ hoạt động đối với các tình huống có hai tên trong cột Tên đầy đủ. Nếu bạn có dữ liệu bao gồm tên đệm hoặc hai tên, thì phương pháp công thức sẽ không hữu ích nếu không thực hiện một số sửa đổi đáng kể đối với công thức. (Thử thách bài tập về nhà. Nếu bạn muốn thử, hãy làm như vậy và cho chúng tôi biết kết quả của bạn trong phần. ) Như chúng ta đã thấy trong hướng dẫn Power Query, trên thực tế, bạn có khả năng lấy ra nhiều hơn hai tên bằng kỹ thuật đó

Các cách để tách văn bản

Đây là các liên kết đến các bài viết khác về cách tách văn bản

• Tách ô có văn bản thành cột trong Excel
• Cách tách văn bản trong ô bằng Flash Fill trong Excel
• Cách tách ô và văn bản trong Excel bằng Power Query
• Chia theo Dấu tách thành Hàng (và Cột) bằng Power Query

Phần kết luận

Tôi hy vọng bạn đã học được điều gì đó mới từ bài đăng này và điều đó hữu ích khi bạn chia dữ liệu thành các cột riêng biệt. Nếu bạn có câu hỏi hoặc nhận xét, xin vui lòng

Trước 3 cách kết hợp văn bản trong Excel – Công thức, Hàm & Power Query

Tiếp theo Công thức DUY NHẤT cho các cột không liền kề

Bạn cũng có thể thích

Tham chiếu toàn bộ cột có thể khiến bạn gặp RẮC RỐI với Excel

Thử thách công thức Excel. Biển số chẵn hoặc lẻ

Cách khắc phục và ngăn chặn lỗi #VALUE với VLOOKUP

Khi nào nên sử dụng VLOOKUP thay vì XLOOKUP