Top 3 cách tách tên trong Excel đơn giản mà bạn không nên bỏ qua
|
Riêng dữ liệu dạng họ và tên trong bảng sẽ dễ quan sát hơn nếu chia thành cột họ và cột tên. Excel là một chương trình rất hữu ích giúp dữ liệu bạn trình bày trực quan và rõ ràng hơn. Dưới đây là Top 3 cách tách tên trên Excel đơn giản mà Sforum đã tổng hợp cho bạn Show
cách dùng hộp thoại Find and Replace của Excel để tách tênTách họChỉ có thể tách riêng họ bằng hộp thoại Tìm và Thay thế; Bước 1 là trước tiên sao chép cột Họ và Tên sang cột sau và đặt tên là Họ Bước 2. Để mở hộp thoại Tìm và Thay thế, nhấn Ctrl + H Nhập nội dung sau vào hộp Find what màu trắng. Space (dấu cách), * (Shift + 8), rồi bấm Replace All tách tênBước 1. Đầu tiên, nhân đôi cột Họ và cột Họ bên cạnh, sau đó đổi tên cột Tên Bước 2. Để mở hộp thoại Tìm và Thay thế, nhấn Ctrl + H Nhập nội dung sau vào hộp Find what màu trắng. * (Shift + 8) và Space (dấu cách), sau đó chọn Replace All Cách dùng hàm gộp của excel để tách têntách tênĐể tách họ và tên, hãy kết hợp ba loại chức năng sau theo cách này
Bước 1. Để bắt đầu, hãy nhập hàm sau vào cột tên (cột C2) =RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))) Bước 2. Kéo dấu cộng ở góc dưới bên phải của ô để phân tách kết quả cho các ô sau khi đã hiển thị tại ô C2 trong phần hiển thị kết quả Tách họKết hợp hai chức năng chính sau đây để tách chúng
Bước 1. Để bắt đầu, gõ hàm sau vào cột họ (i. e. , cột B2) =LEFT(A2,LEN(A2)-LEN(C2)) Bước 2. Kéo dấu cộng ở góc dưới bên phải của ô để tách tên các ô sau khi có kết quả hiển thị ở ô B2 ở bước 1 Cách dùng công cụ Text to Columns của Excel để tách tênBước đầu tiên các bạn chọn cột Họ chứa thông tin muốn tách Bước 2. Trên tab Dữ liệu, chọn công cụ Chuyển văn bản thành Cột Bước 3. Chọn Delimited rồi nhấn Next sau khi hộp thoại mở ra Bước 4. Nhấp vào Tiếp theo sau khi chọn Dấu cách trong Dấu phân cách Bước 5. Nhấp vào Kết thúc sau khi chọn Văn bản trong hộp thoại cuối cùng và nhập ô sẽ chứa dữ liệu sau khi tách trong hộp trống Đích Sử dụng tính năng Flash Fill, tách chúng raChỉ có thể sử dụng các thao tác sau để tách chúng bằng tính năng này Bước 1. Nhập họ vào hai dòng đầu tiên của cột Họ Bước 2. Bên dưới hai dòng này, bạn sẽ thấy danh sách họ trùng với họ ở cột Họ và Tên. Sau khi xác minh rằng điều này là chính xác, chỉ cần nhấn Enter để tách họ kết thúcNếu bạn có bất kỳ câu hỏi nào, đừng ngần ngại hỏi họ trong phần bình luận bên dưới. Trên đây là bài viết tổng hợp 3 cách đơn giản mà Sforum muốn chia sẻ với các bạn để tách họ và tên trên Excel Có rất nhiều công thức hữu ích cũng như các chức năng mà bạn có thể sử dụng để làm việc với dữ liệu văn bản trong Excel Một trong những câu hỏi rất phổ biến mà tôi nhận được khi thao tác với dữ liệu văn bản là – “Làm cách nào để tách biệt họ và tên (hoặc tên, đệm và họ) trong Excel?” Có một số cách dễ dàng để tách tên trong Excel. Phương pháp bạn chọn sẽ tùy thuộc vào cách dữ liệu của bạn được cấu trúc và liệu bạn muốn kết quả cowothe là tĩnh hay động Vì vậy, hãy bắt đầu và xem các cách khác nhau để tách tên trong Excel Tách tên bằng cách sử dụng văn bản thành cộtChức năng Text to Columns trong Excel cho phép bạn nhanh chóng chia các giá trị văn bản thành các ô riêng biệt trong một hàng Ví dụ: giả sử bạn có tập dữ liệu như hình bên dưới và bạn muốn tách họ và tên và lấy chúng trong các ô riêng biệt Dưới đây là các bước tách họ và tên bằng Text to Columns
Các bước trên sẽ ngay lập tức chia tên thành họ và tên (với tên ở cột B và họ ở cột C) Ghi chú. Trong trường hợp đã có bất kỳ dữ liệu nào trong các ô (những ô dự kiến sẽ xuất Văn bản thành Cột), Excel sẽ hiển thị cho bạn một cảnh báo cho bạn biết rằng đã có một số dữ liệu trong các ô. Bạn có thể chọn ghi đè dữ liệu hoặc hủy Văn bản thành Cột và xóa thủ công trước Sau khi hoàn tất, bạn có thể xóa dữ liệu họ và tên nếu muốn Một số điều cần biết khi sử dụng Text to Columns để tách họ và tên trong Excel
Tùy chọn Văn bản thành Cột phù hợp nhất khi bạn có dữ liệu nhất quán (ví dụ: tất cả các tên chỉ có họ và tên hoặc tất cả các tên đều có họ, đệm và tên) Trong ví dụ này, tôi đã chỉ cho bạn cách tách các tên có khoảng trắng làm dấu phân cách. Trường hợp dấu phân cách là dấu phẩy hoặc kết hợp giữa dấu phẩy và dấu cách, bạn vẫn có thể thực hiện các bước tương tự. Trong trường hợp này, bạn có thể chỉ định dấu phân cách trong Bước 2/3 của trình hướng dẫn. Đã có tùy chọn để sử dụng dấu phẩy làm dấu phân cách hoặc bạn có thể chọn tùy chọn 'Khác' và chỉ định cả dấu phân cách tùy chỉnh Mặc dù Chuyển văn bản thành Cột là một cách nhanh chóng và hiệu quả để tách tên, nhưng nó chỉ phù hợp khi bạn muốn đầu ra là kết quả tĩnh. Trong trường hợp bạn có một tập dữ liệu có thể mở rộng hoặc thay đổi, tốt hơn hết bạn nên sử dụng các công thức để tách tên Tách Tên, Tên đệm và Họ bằng Công thứcCác công thức cho phép bạn chia nhỏ dữ liệu văn bản và trích xuất những gì bạn muốn Trong phần này, tôi sẽ chia sẻ các công thức khác nhau mà bạn có thể sử dụng để tách dữ liệu tên (dựa trên cách dữ liệu của bạn được cấu trúc) Dưới đây là ba công thức bạn có thể sử dụng để tách tên, tên đệm và họ (được giải thích chi tiết sau trong các phần sau) Công thức để có được tên đầu tiên =LEFT(A2,SEARCH(" ",A2)-1)Công thức lấy tên đệm =MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",SUBSTITUTE(A2," ","@",1))-SEARCH(" ",A2))Công thức lấy họ =RIGHT(A15,LEN(A15)-SEARCH("@",SUBSTITUTE(A15," ","@",LEN(A15)-LEN(SUBSTITUTE(A15," ","")))))Lấy tênGiả sử bạn có tập dữ liệu như hình bên dưới và bạn muốn tách nhanh họ và tên 1 ô Công thức dưới đây sẽ cung cấp cho bạn tên đầu tiên =LEFT(A2,SEARCH(" ",A2)-1)Công thức trên sử dụng hàm SEARCH để lấy vị trí của ký tự khoảng trắng giữa họ và tên. Sau đó, hàm LEFT sử dụng số vị trí khoảng trắng này để trích xuất tất cả văn bản trước nó Đây là một cách sử dụng khá đơn giản để trích xuất một phần giá trị văn bản. Vì tất cả những gì chúng ta cần làm là xác định vị trí ký tự khoảng trắng đầu tiên, nên tên có đệm hay không không quan trọng. Công thức trên sẽ hoạt động tốt Bây giờ, hãy nâng cao hơn một chút với từng ví dụ Lấy HọGiả sử bạn có cùng tập dữ liệu và lần này bạn cần lấy họ Công thức dưới đây sẽ trích xuất họ từ tập dữ liệu trên =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))Một lần nữa, khá đơn giản Lần này, trước tiên chúng tôi tìm vị trí ký tự khoảng trắng, sau đó được sử dụng để tìm số ký tự còn lại sau khoảng trắng (có thể là họ) Điều này đạt được bằng cách trừ đi giá trị vị trí của ký tự khoảng trắng với tổng số ký tự trong tên Số này sau đó được sử dụng trong hàm RIGHT để tìm nạp tất cả các ký tự này từ bên phải của tên Mặc dù công thức này hoạt động tốt khi chỉ có họ và tên, nhưng nó sẽ không hoạt động nếu bạn cũng có tên đệm. Điều này là do chúng tôi chỉ chiếm một ký tự khoảng trắng (giữa họ và tên). Có tên đệm thêm nhiều ký tự khoảng trắng vào tên Để lấy họ khi bạn cũng có tên đệm, hãy sử dụng công thức dưới đây =RIGHT(A15,LEN(A15)-SEARCH("@",SUBSTITUTE(A15," ","@",LEN(A15)-LEN(SUBSTITUTE(A15," ","")))))Bây giờ, điều này đã bắt đầu trở nên hơi phức tạp… phải không? Hãy để tôi giải thích làm thế nào điều này hoạt động Công thức trên trước tiên tìm tổng số ký tự khoảng trắng trong tên. Điều này được thực hiện bằng cách lấy độ dài của tên có và không có ký tự khoảng trắng, sau đó trừ tên không có khoảng trắng khỏi tên có khoảng trắng. Điều này cho tổng số ký tự khoảng trắng Hàm SUBSTITUTE sau đó được sử dụng để thay thế ký tự khoảng trắng cuối cùng bằng ký hiệu '@' (bạn có thể sử dụng bất kỳ ký hiệu nào – ký hiệu ít có khả năng xuất hiện như một phần của tên) Khi biểu tượng @ đã được thay thế cho ký tự khoảng trắng cuối cùng, bạn có thể dễ dàng tìm thấy vị trí của biểu tượng @ này. Điều này được thực hiện bằng chức năng TÌM KIẾM Bây giờ tất cả những gì bạn cần làm là giải nén tất cả các ký tự bên phải ký hiệu @ này. Điều này được thực hiện bằng cách sử dụng chức năng RIGHT Lấy tên đệmGiả sử bạn có bộ dữ liệu như hình bên dưới và bạn muốn trích xuất tên đệm Công thức sau đây sẽ làm điều này =MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",SUBSTITUTE(A2," ","@",1))-SEARCH(" ",A2))Công thức trên sử dụng hàm MID, cho phép bạn chỉ định vị trí bắt đầu và số lượng ký tự để trích xuất từ vị trí đó Dễ dàng tìm thấy vị trí bắt đầu bằng chức năng TÌM KIẾM Phần khó là tìm xem có bao nhiêu ký tự cần trích xuất sau vị trí bắt đầu này. Để có được điều này, bạn cần xác định có bao nhiêu ký tự từ vị trí bắt đầu đến ký tự khoảng trắng cuối cùng Điều này có thể được thực hiện bằng cách sử dụng hàm SUBSTITUTE và thay thế ký tự khoảng trắng cuối cùng bằng ký hiệu '@'. Khi điều này được thực hiện, bạn có thể dễ dàng sử dụng chức năng TÌM KIẾM để tìm vị trí của ký tự khoảng trắng cuối cùng này Bây giờ bạn đã có vị trí bắt đầu và vị trí của khoảng trắng cuối cùng, bạn có thể dễ dàng tìm nạp tên đệm bằng cách sử dụng hàm MID Một trong những lợi ích của việc sử dụng công thức để tách tên là kết quả là động. Vì vậy, trong trường hợp, tập dữ liệu của bạn mở rộng và nhiều tên hơn được thêm vào hoặc nếu một số tên thay đổi trong tập dữ liệu gốc, bạn không cần phải lo lắng về dữ liệu kết quả Tách tên bằng cách sử dụng Tìm và Thay thếTôi thích sự linh hoạt đi kèm với 'Tìm và Thay thế' – bởi vì bạn có thể sử dụng các ký tự đại diện trong đó Trước tiên hãy để tôi giải thích ký tự đại diện là gì Ký tự đại diện là thứ bạn có thể sử dụng thay cho bất kỳ văn bản nào. Ví dụ: bạn có thể sử dụng ký hiệu dấu hoa thị (*) và ký hiệu này sẽ đại diện cho bất kỳ số lượng ký tự nào trong Excel. Để cho bạn một ví dụ, nếu tôi muốn tìm tất cả các tên bắt đầu bằng bảng chữ cái A, tôi có thể sử dụng A* để tìm và thay thế. Thao tác này sẽ tìm và chọn tất cả các ô có tên bắt đầu bằng A Nếu bạn vẫn chưa rõ, đừng lo lắng. Hãy tiếp tục đọc và một vài ví dụ tiếp theo sẽ làm rõ ký tự đại diện là gì và cách sử dụng chúng để nhanh chóng phân tách tên (hoặc bất kỳ giá trị văn bản nào trong Excel) Trong tất cả các ví dụ được đề cập bên dưới, hãy đảm bảo bạn tạo một bản sao dự phòng của tập dữ liệu. Tìm và Thay thế thay đổi dữ liệu được sử dụng. Tốt nhất là sao chép và dán dữ liệu trước, sau đó sử dụng Tìm và Thay thế trên tập dữ liệu đã sao chép Lấy tênGiả sử bạn có tập dữ liệu như hình bên dưới và bạn chỉ muốn lấy tên Dưới đây là các bước để làm điều này
Các bước trên sẽ cung cấp cho bạn tên và xóa mọi thứ sau tên Điều này hoạt động ngay cả khi bạn có tên có tên đệm Mẹo chuyên nghiệp. Phím tắt để mở hộp thoại Tìm và Thay thế là Control + H (giữ phím điều khiển rồi nhấn phím H) Lấy HọGiả sử bạn có tập dữ liệu như hình bên dưới và bạn chỉ muốn lấy họ Dưới đây là các bước để làm điều này
Các bước trên sẽ cung cấp cho bạn họ và xóa mọi thứ trước tên Điều này hoạt động ngay cả khi bạn có tên có tên đệm Xóa tên đệmTrong trường hợp bạn chỉ muốn bỏ tên đệm và chỉ còn họ và tên, bạn có thể thực hiện việc đó bằng Find and Replace Giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn xóa tên đệm khỏi các Dưới đây là các bước để làm điều này
Các bước trên sẽ loại bỏ tên đệm khỏi tên đầy đủ. Trường hợp một số tên không có đệm thì không đổi Tách tên bằng Flash FillĐiền flash đã được giới thiệu trong Excel 2013 và giúp dễ dàng sửa đổi hoặc xóa tập dữ liệu văn bản Và khi nói đến việc tách dữ liệu tên, nó nằm ngay trong con hẻm của Flash Fill Điều quan trọng nhất cần biết khi sử dụng Flash Fill là cần có một mẫu mà flash fill có thể xác định được. Khi đã xác định được mẫu, nó sẽ dễ dàng giúp bạn tách tên trong Excel (bạn sẽ rõ hơn về điều này khi xem qua một số ví dụ bên dưới) Lấy Tên hoặc Họ từ Tên đầy đủGiả sử bạn có tập dữ liệu như hình bên dưới và bạn chỉ muốn lấy tên
Flash Fill cần bạn cung cấp cho nó một mẫu để nó có thể tuân theo khi cung cấp cho bạn dữ liệu đã sửa đổi. Trong ví dụ của chúng tôi, khi bạn nhập tên vào ô đầu tiên, Flash Fill không thể tìm ra mẫu Nhưng ngay khi bạn bắt đầu nhập Tên vào ô thứ hai, Flash Fill sẽ hiểu mẫu và hiển thị cho bạn một số gợi ý. Nếu đề xuất là chính xác, chỉ cần nhấn phím enter Và nếu nó không chính xác, bạn có thể thử nhập thủ công vào một vài ô nữa và kiểm tra xem Flash Fill có thể phân biệt mẫu hay không Đôi khi, bạn có thể không nhìn thấy mẫu có màu xám (như trong bước 2 ở trên). Nếu đúng như vậy, hãy làm theo các bước bên dưới để nhận kết quả Flash Fill
Các bước trên sẽ cung cấp cho bạn kết quả từ Flash Fill (dựa trên mẫu mà nó đã suy ra) Bạn cũng có thể sử dụng Flash Fill để lấy họ hoặc tên đệm. 2 ô đầu nhập họ (hoặc tên lót) và flash fill sẽ hiểu mẫu Sắp xếp lại tên bằng Flash FillFlash Fill là một công cụ thông minh và nó cũng có thể giải mã các mẫu hơi phức tạp Ví dụ: giả sử bạn có tập dữ liệu như hình bên dưới và bạn muốn sắp xếp lại tên từ Rick Novak thành Novak, Rick (trong đó họ đứng trước, sau đó là dấu phẩy và sau đó là tên) Dưới đây là các bước để làm điều này
Bỏ Tên Đệm (hoặc Chỉ lấy tên đệm)Bạn cũng có thể sử dụng Flash Fill để loại bỏ tên đệm hoặc chỉ lấy tên đệm Ví dụ: giả sử bạn có tập dữ liệu như hình bên dưới và bạn chỉ muốn lấy họ và tên chứ không lấy tên đệm Dưới đây là các bước để làm điều này
Tương tự, nếu bạn chỉ muốn lấy tên đệm, hãy nhập tên đệm vào 2 ô đầu tiên và sử dụng Flash Fill để lấy tên đệm từ tất cả các tên còn lại Các ví dụ hiển thị trong hướng dẫn này sử dụng tên trong khi thao tác dữ liệu văn bản. Bạn có thể sử dụng các khái niệm tương tự để làm việc với các định dạng dữ liệu khác (chẳng hạn như địa chỉ, tên sản phẩm, v.v. ) Bạn cũng có thể thích các hướng dẫn Excel sau đây
SÁCH EXCEL MIỄN PHÍ Nhận Ebook 51 mẹo Excel để tăng năng suất và hoàn thành công việc nhanh hơn
Tên CÓ - GỬI SÁCH ĐIỆN TỬ CHO TÔI 5 suy nghĩ về “Tách Họ và Tên trong Excel (Tách Tên Bằng Công Thức)”
|
