Cách tách chuỗi văn bản thành hai cộ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

Xem trên YouTube và Đăng ký Kênh của chúng tôi

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

Chia-Tên-với-Công Thức-BEGIN. xlsx

Chia-Tên-với-Công Thức-CUỐI CÙNG. xlsx

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, phương thức 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

Sự 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

Hans Hallebeek nói.

Còn những cái tên có van hoặc von thì sao?
Peter van Delft thì sao?

• Jon Acampora nói.

  Câu hỏi hay, Hans. Những loại tên đó có thể khó. Trong bài viết tách tên bằng Power Query, tôi đã giải thích cách tách ô có nhiều hơn hai tên

  https. //www. khuôn viên excel. com/powerquery/split-column-power-query/

  Thách thức là nếu cột chứa sự kết hợp của tên đệm và hai họ và/hoặc hai tên, v.v.

  Nếu bạn CHỈ gặp các trường hợp có hai họ, thì bạn có thể sử dụng kỹ thuật Power Query

  Trong một bài đăng trong tương lai, chúng tôi sẽ xem xét cách tách tên bằng Flash Fill, điều này cũng có thể giúp ích cho những tình huống phức tạp hơn đó

  Tôi hy vọng điều đó sẽ giúp. Cảm ơn một lần nữa và chúc một ngày tốt lành. 🙂

Rick nói.

Tôi sử dụng chức năng bên trái đầu tiên được đề cập để lấy tên đầu tiên, sau đó tôi sử dụng chức năng thay thế để lấy các tên khác. Đối với 3 tên, tôi ghép các từ thay thế

• Jon Acampora nói.

  Đó là một kỹ thuật công thức tuyệt vời khác để tách tên. Cảm ơn đã chia sẻ, Rick. 🙂

Abbott Katz nói.

Có thể đạt được các phần tách này trong Excel 365, giả sử các tên được tách được định vị trong các ô A4. A6, thông qua hai công thức này.
=LEFT(A4. A6,TÌM(” “,A4. A6))
=RIGHT(A4. A6,LEN(A4. A6)-TÌM(” “,A4. A6))

Bạn không thể viết công thức mảng động trong bảng Excel, vì vậy bạn có thể đặt tên cho phạm vi ô chứa tên thông qua OFFSET. Theo cách đó, các tên được thêm vào phạm vi sẽ tự động nhận được các công thức chia

Ngoài ra còn có chức năng Chia của Google Trang tính

• Jon Acampora nói.

  Đáng kinh ngạc. Cảm ơn Abbott đã chia sẻ. Vâng, hy vọng, Excel cũng sẽ sớm có chức năng Chia

Heinz nói.

Tôi đã gửi cho bạn trong số cuối cùng một số hạt cứng tuyệt vời để bẻ khóa

Tên đệm viết tắt, đôi khi có đôi khi không. > bạn có thể thử tìm kiếm “. ” với if, mid, len left và right
Họ kép của Đức và Thụy Sĩ như
Anneliese Leuschner-Schayan, nhưng ở Thụy Sĩ đôi khi không có “-”< . Jörg Mittelsten Scheidt (Tiến sĩ. và Mittelsten là tước vị, nhưng Mittelsten phải ở họ)
Heinrich Graf von Wolkenburg > ?? > Only Heinrich is First Name
Dr. Jörg Mittelsten Scheidt (Dr. and Mittelsten are titles, but Mittelsten must be at the family name)

Tại sao không tạo ra một cuộc thi từ vấn đề này 🙂

• Jon Acampora nói.

  Cảm ơn Heinz. Đó là một trong những khó khăn để crack. Trong bài đăng của tuần tới, chúng ta sẽ xem xét Flash Fill với các tình huống phức tạp hơn như thế này và tôi cũng mở ra để nhận các đề xuất. Tôi thích ý tưởng về một cuộc thi mặc dù. Chúng tôi sẽ làm điều đó trong tương lai. Cảm ơn một lần nữa

Taruna nói.

Có một cái gì đó mới để học. Cảm ơn

• Jon Acampora nói.

  Vui khi nghe nó. Cảm ơn Taruna. 🙂

Arun Bhadra Khanal nói.

Tại ô A1. D1, nhập văn bản. "Họ và tên"; .
Data start from the second row.

Gọi “Họ Tên” tại Ô A2 là.
Jenifer Anne Drayn

Công thức tại ô B2 (đối với Tên) là.
=LEFT(A2,FIND(” “,A2)-1)

Kết quả. Jenifer

Công thức tại ô D2 (cho Họ) là.
=RIGHT(A2,LEN(A2)-FIND(“”*””,SUBSTITUTE(A2,”” “”,””*””,LEN(A2)-
LEN(SUBSTITUTE(A2,”” “”,””””)))))”

Kết quả. Drayn

Công thức tại ô C2 (cho Tên đệm) là.
=IF(LEN(B2&D2)+2>=LEN(A2),””,MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2&D2)-2

Kết quả. Anne

• Jon Acampora nói.

  Cảm ơn arun đã chia sẻ. 🙂

  • Willard Stevenson nói.

    Tôi muốn bao gồm TRIM và/hoặc CLEAN ở những nơi thích hợp khi phân tích cú pháp văn bản. Hãy nhận biết khoảng cách đôi giữa văn bản hoặc ký tự vô hình, đặc biệt là khi dán từ các nguồn khác

Dan nói.

Xin chào,
Tôi tin rằng Flash fill có thể tách văn bản rất tốt. Rất hiệu quả – giúp bạn tiết kiệm công sức tìm ra công thức.

Dickson Makwinja nói.

Tuyệt vời

Simon Kerr nói.

HI JON,
Làm cách nào chúng tôi có thể thiết lập họ với các khoảng trống, ví dụ:
Bob Du Plessis hoặc Koos van der Merwe
Can we write a formula looking ONLY for the first space?

علاء عبد الغنى nói.

cảm ơn bạn vì video tuyệt vời này. nó mở mang đầu óc để suy nghĩ theo một cách khác. và giúp đỡ trong công việc thực tế

Bạn có thể tách một chuỗi trong Excel không?

Để tách chuỗi trong Excel, bạn thường sử dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH .

Công thức tách văn bản trong Excel là gì?

Trong cột trống đầu tiên, viết =SPLIT(B1,"-") , với B1 là ô bạn muốn tách và .