Thủ thuật it



truy Lượt Xem:10330

Hướng dẫn cách tách họ và tên thành các cột khác nhau trong excel

Hướng dẫn này cho thấy cách tách biệt họ và tên trong Excel bằng công thức hoặc Văn bản thành Cột và cách phân tách nhanh một cột tên theo nhiều định dạng khác nhau thành tên, họ và cuối cùng, các lời chào và hậu tố.
 
Đây là một tình huống rất phổ biến trong Excel mà bảng tính của bạn có chứa một cột tên đầy đủ, và bạn muốn tách họ và tên thành các cột riêng biệt. Nhiệm vụ có thể được thực hiện theo một vài cách khác nhau - bằng cách sử dụng tính năng Text to Columns, công thức và công cụ Split Names. Dưới đây bạn sẽ tìm thấy đầy đủ chi tiết về từng kỹ thuật.
 
Cách tách tên đầy đủ bằng tính năng Văn bản thành Cột
 
Cách tách tên trong Excel bằng công thức
 
Phân tách họ và tên từ tên đầy đủ với không gian
 
Tách biệt họ và tên từ tên bằng dấu phẩy
 
Tách tên, họ và tên đệm
 
Tên riêng trong Excel 2013 và 2016 với Flash Fill
 
Công cụ tách tên - cách nhanh nhất để tách tên trong Excel

Cách tách tên trong Excel bằng Văn bản thành Cột

Trong các tình huống khi bạn có một cột tên của cùng một mẫu, ví dụ chỉ có họ và tên, hoặc tên, họ và tên, cách dễ nhất để tách chúng thành các cột riêng biệt là:
 
Chọn cột tên đầy đủ mà bạn muốn tách biệt.
 
Đi đến dữ liệu tab> Dữ liệu nhóm và nhấp vào Text to Columns .
 
huong-dan-cac-tach-ho-va-ten-thanh-cac-cot-khac-nhau-trong-excel
 
 
Trong bước đầu tiên của thuật sĩ Chuyển văn bản thành cột , chọn tùy chọn Delimited và nhấn Next .
 
huong-dan-cac-tach-ho-va-ten-thanh-cac-cot-khac-nhau-trong-excel
 
Trong bước tiếp theo, chọn một hoặc nhiều dấu phân tách và bấm Tiếp theo .

 

Trong trường hợp của chúng tôi, các phần khác nhau của tên được phân cách bằng dấu cách, vì vậy chúng tôi chọn dấu phân cách này. Phần xem trước dữ liệu cho thấy rằng tất cả các tên của chúng tôi được phân tích cú pháp tốt.

huong-dan-cac-tach-ho-va-ten-thanh-cac-cot-khac-nhau-trong-excel

 

Tiền boa. Nếu bạn đang đối phó với tên tách ra với một dấu phẩy và không gian như Anderson, Ronnie , sau đó kiểm tra Comma và Space hộp dưới ký tự phân cách , và chọn delimiters Treat liên tục là một hộp kiểm (thường chọn mặc định).
Ở bước cuối cùng, bạn chọn định dạng dữ liệu và đích , sau đó nhấn Finish .
 
Định dạng chung mặc định hoạt động tốt trong hầu hết các trường hợp. Là Điểm đến , hãy chỉ định ô trên cùng trong cột nơi bạn muốn xuất kết quả (xin lưu ý rằng điều này sẽ ghi đè bất kỳ dữ liệu hiện có nào, vì vậy hãy đảm bảo chọn một cột trống).

huong-dan-cac-tach-ho-va-ten-thanh-cac-cot-khac-nhau-trong-excel

 

Làm xong! Tên đầu tiên, giữa và họ được chia thành các cột riêng biệt:

Tên đầu tiên, giữa và họ

 

Tách biệt họ và tên trong Excel bằng công thức

Như bạn vừa thấy, tính năng Text to Columns rất nhanh chóng và dễ dàng. Tuy nhiên, nếu bạn dự định thực hiện bất kỳ thay đổi nào đối với tên gốc và đang tìm kiếm giải pháp động sẽ tự động cập nhật, bạn nên phân chia tên bằng công thức.

 

Cách tách tên và họ từ tên đầy đủ bằng dấu cách
 
Các công thức này bao gồm kịch bản tiêu biểu nhất khi bạn có tên và họ trong một cột được phân tách bằng một ký tự khoảng trắng .
 
Công thức để lấy tên
 
Tên đầu tiên có thể dễ dàng được trích xuất bằng công thức chung này:
 
TRÁI ( ô , TÌM KIẾM ("", ô ) - 1)
 
Bạn sử dụng TÌM KIẾM  hoặc FIND chức năng để có được vị trí của nhân vật không gian (" ") trong một tế bào, từ đó bạn trừ 1 để loại trừ không gian riêng của mình. Con số này được cung cấp cho hàm LEFT khi số lượng ký tự được trích xuất, bắt đầu từ phía bên trái của chuỗi.
 
Công thức lấy họ
 
Công thức chung để trích xuất họ là:
 
QUYỀN ( ô , LEN ( ô ) - TÌM KIẾM ("",  ô ))
 
Trong công thức này, bạn cũng sử dụng hàm TÌM KIẾM để tìm vị trí của khoảng trắng, trừ số đó khỏi tổng chiều dài của chuỗi (được trả về bởi LEN ), và nhận hàm RIGHT để trích xuất nhiều ký tự từ bên phải của chuỗi.
 
Với tên đầy đủ trong ô A2, các công thức sẽ như sau:
 
Lấy tên đầu tiên :
 
=LEFT(A2,SEARCH(" ",A2)-1)
 
Lấy tên cuối cùng :
 
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
 
Bạn nhập các công thức trong ô B2 và C2 tương ứng, và kéo núm điều khiển điền để sao chép các công thức xuống các cột. Kết quả sẽ trông giống như sau:

huong-dan-cac-tach-ho-va-ten-thanh-cac-cot-khac-nhau-trong-excel

 

Nếu một số tên gốc chứa tên đệm hoặc tên đệm ở giữa , bạn cần công thức phức tạp hơn một chút để trích xuất họ:
 
=RIGHT(A2, LEN(A2) - SEARCH("#", SUBSTITUTE(A2," ", "#", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))
 
Đây là giải thích mức cao về logic của công thức: bạn thay thế khoảng trống cuối cùng trong tên bằng dấu băm (#) hoặc bất kỳ ký tự nào khác không xuất hiện trong bất kỳ tên nào và tìm ra vị trí của char đó. Sau đó, bạn trừ số trên khỏi tổng chiều dài chuỗi để lấy độ dài của họ, và có hàm RIGHT trích xuất nhiều ký tự.
 
Vì vậy, đây là cách bạn có thể tách họ và tên trong Excel khi một số tên gốc bao gồm tên đệm:
 
huong-dan-cac-tach-ho-va-ten-thanh-cac-cot-khac-nhau-trong-excel
 

Cách tách tên và họ của tên bằng dấu phẩy

 
Nếu bạn có một cột tên trong họ , định dạng tên đầu tiên , bạn có thể chia chúng thành các cột riêng biệt bằng cách sử dụng các công thức sau đây.
 
Công thức trích xuất tên đầu tiên
 
QUYỀN ( ô , LEN ( ô ) - TÌM KIẾM ("",  ô ))
 
Giống như trong ví dụ trên, bạn sử dụng hàm TÌM KIẾM để xác định vị trí của một ký tự khoảng trắng, và sau đó trừ nó khỏi tổng chiều dài chuỗi để có được độ dài của tên đầu tiên. Con số này đi trực tiếp đến đối số num_chars của hàm RIGHT cho biết có bao nhiêu ký tự để trích xuất từ ​​cuối chuỗi.
 
Công thức trích xuất họ
 
TRÁI ( ô , TÌM KIẾM ("", ô ) - 2)
Để có được họ, bạn sử dụng kết hợp TÌM KIẾM TRÌ được thảo luận trong ví dụ trước với sự khác biệt mà bạn trừ 2 thay vì 1 để tính thêm hai ký tự, dấu phẩy và dấu cách.
 
Với tên đầy đủ trong ô A2, các công thức có hình dạng sau:
 
Lấy tên đầu tiên :
 
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))
 
Lấy tên cuối cùng :
 
=LEFT(A2, SEARCH(" ", A2) - 2)
 
Ảnh chụp màn hình dưới đây cho thấy kết quả:

Công thức trích xuất họ

 

Cách tách tên đầy đủ thành tên, họ và tên đệm

Việc tách tên bao gồm tên đệm hoặc chữ viết tắt ở giữa yêu cầu các cách tiếp cận hơi khác nhau, tùy thuộc vào định dạng tên.
 
Nếu tên của bạn có tên First name Tên cuối cùng định dạng, các công thức dưới đây sẽ làm việc một điều trị:
 
Cách tách tên đầy đủ thành tên, họ và tên đệm
 
Để có được tên đầu tiên , bạn sử dụng công thức LEFT SEARCH đã quen thuộc.
 
Để có được tên cuối cùng , xác định vị trí của 2 nd không gian bằng cách sử dụng chức năng TÌM KIẾM lồng nhau , trừ các vị trí từ tổng chiều dài chuỗi, và nhận được độ dài của tên cuối cùng là kết quả. Sau đó, bạn cung cấp số ở trên cho hàm RIGHT hướng dẫn nó kéo số ký tự đó từ cuối chuỗi.
 
Để trích xuất tên đệm , bạn cần biết vị trí của cả hai dấu cách trong tên. Để xác định vị trí của không gian đầu tiên, hãy sử dụng hàm SEARCH ("", A2) đơn giản, mà bạn thêm 1 để bắt đầu trích xuất với ký tự tiếp theo. Số này đi đến đối số start_num của hàm MID . Để tính toán chiều dài của tên đệm, bạn trừ vị trí của không gian thứ nhất từ ​​vị trí của không gian thứ hai, trừ 1 từ kết quả để loại bỏ dấu cách, và đặt số này vào đối số num_chars của MID, cho biết số lượng ký tự cần trích xuất.
 
huong-dan-cac-tach-ho-va-ten-thanh-cac-cot-khac-nhau-trong-excel
 
Và đây là các công thức để phân tách tên Họ, Tên đệm Tên đệm:
 
 
A B C D
1 Họ và tên Tên đầu tiên Tên đệm Họ
2 LastName, FirstName Tên đệm =MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) + 1) - SEARCH(" ", A2) -1) =RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2, 1)+1)) =LEFT(A2, SEARCH(" ",A2,1)-2)
Kết quả: Trắng, David Mark David dấu trắng

Một cách tiếp cận tương tự có thể được sử dụng để phân chia tên với hậu tố:

  A B C D
1 Họ và tên Tên đầu tiên Họ Hậu tố
2 Tên họ, Hậu tố =LEFT(A2, SEARCH(" ",A2)-1) =MID(A2, SEARCH(" ",A2) + 1, SEARCH(",",A2) - SEARCH(" ",A2)-1) =RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ",A2)+1))
Kết quả: Robert Furlan, Jr. Robert Furlan Jr.

Đó là cách bạn có thể phân chia tên trong Excel bằng cách sử dụng các kết hợp hàm khác nhau. Để hiểu rõ hơn và có thể ngược lại các công thức, bạn có thể tải xuống sổ làm việc mẫu của chúng tôi để phân tách các tên trong Excel .

Mời bạn xem thêm:    sắp xếp các ô theo màu sắc trong excel  && cách sử dụng goal seek trong excel 2010


Tags: tách họ, tên và tên lót ; tách họ tên trong excel ;
Bình luận

Bình luận

Các bài viết mới

Các tin cũ hơn