Tách họ và tên trong Excel

Cập nhật ngày 30, Tháng Chín, 2016, Thuộc Office, Thủ thuật Excel, Tin học văn phòng

Bạn có thể tải về máy file PDF nội dung tài liệu này tại đây: Tải về file PDF

Các bạn làm tin học văn phòng rất thường hay gặp trường hợp yêu cầu tách họ và tên ra thành 2 ô riêng biệt từ 1 ô nhập liệu sẵn.

Bài này trungtamtinhoc.edu.vn sẽ hướng dẫn các bạn cách xử lý vấn đề này. Qua đó, chúng ta sẽ học và củng cố lại một số kiến thức cũ.

Các kiến thức trong bài này chúng ta sẽ sử dụng gồm:

  • Tìm và thay dùng ký tự đại diện (Find & replace)
  • Xử lý chuỗi kí tự bằng hàm LEFT, LEN

Với mỗi bước làm, mình sẽ cố gắn nói tỉ mỉ và giải thích cặn kẽ, bạn nào chưa hiểu chỗ nào thì comment bên dưới nhé.

Cách này tất nhiên áp dụng được với mọi phiên bản Excel.

Dữ liệu ban đầu

Dữ liệu ban đầu chỉ có cột Họ và tên chung nhau trong một cột A. Yêu cầu tách Tên ra riêng một cột C, Họ & tên đệm ra riêng một cột khác (cột D).

tach-ho-va-ten-trong-excel

Cách Tách họ và tên thành cột riêng

Bước 1: Copy dữ liệu để trích lấy phần Tên

Ta bôi đen chọn vùng dữ liệu Họ và Tên (vùng A2 đến A7) rồi ấn CTRL+C để sao chép sang cột D

copy-ho-va-ten

Tại cột D, click đặt con trỏ vào ô D2 rồi Ấn CTRL+V để dán dữ liệu vào, ta sẽ được kết quả như hình trên.

Bước 2: Dùng chức năng Find & Replace để xóa phần Học & tên lót, chỉ lấy phần tên

  • Chọn vùng dữ liệu trong cột Tên, từ D2 đến D7
  • Ấn CTRL + H để mở hộp thoại Find & Replace với chức năng tìm và thay thế
  • Tại ô Find what, gõ vào *_ (_ tức là khoảng trống, mình gõ _ cho bạn dễ thấy, xem hình vẽ).
  • Tại ô Replace with, các bạn bỏ trống, tức không có gì.
  • Click nút Replace All để tiến hành thay thế

Việc này có ý nghĩa ra lệnh rằng: Hãy tìm trong những ô thuộc vùng tôi chọn những cụm từ bất kỳ nào kết thúc bằng một “khoảng trắng” (*_) rồi thay nó bằng không có gì

Hay nói cách khác đi, nó sẽ xóa bỏ chuỗi trong ô tính từ đầu đến khoảng trắng cuối cùng. Phần còn lại đó chính là phần Tên trong cấu trúc Họ & Tên

thay-the-phan-ho-de-lay-phan-ten-excel

Kết quả đạt được như hình dưới, click OK để xác nhận:

tach-lay-phan-ten

Bonus: Excel nó báo là nó đã thay tổng cộng 14 nơi. Các bạn suy nghĩ xem tại sao lại là 14 nơi, trong khi mình chỉ có 6 Họ & Tên? Comment nhé. Bạn nào trả lời đúng đầu tiên mình sẽ tặng một tài khoản học tin học văn phòng online trị giá 200k. PM facebook mình để nhận nhé.

Bước 3: Tách lấy phần Họ & tên lót

Ta đã có phần Tên. Bây giờ mình sẽ tách lấy phần Họ & Tên đệm bằng cách trừ bỏ phần Tên trong cả chuỗi Họ & Tên.

Muốn thế, mình dùng hàm LEFT (Xem thêm: Ví dụ về Hàm IF Kết Hợp LEFT, RIGHT, MID Trong Excel).

Trong trường hợp này, Ví dụ Họ & Tên trong hàng thứ 2 là “Ngô Vĩnh Tâm”, phần Họ và tên đệm cần lấy là “Ngô Vĩnh”

Tức là ta cần lấy từ bên trái qua x ký tự. Với x chính là số ký tự của phần Họ & Tên đệm, nó chính là số ký tự của cả chuỗi Họ & tên trừ đi số ký tự của Tên rồi trừ đi 1 khoảng trắng nữa.

Vậy công thức chính xác để tính số ký tự của phần Họ & tên đệm (x) sẽ là:

► NỘI DUNG DÀNH RIÊNG ĐÃ ĐƯỢC ẨN ĐI ◄
Vui lòng click nút chia sẻ lên Facebook để xem nội dung ẩn

arrow
f Chia sẻ lên Facebookt Chia sẻ lên Twiter

Trong đó, hàm LEN là hàm tính chiều dài của chuỗi ký tự (Xem thêm: Cách đếm số ký tự và số chữ dùng hàm LEN)

  • LEN(A2): Chiều dài của ô Họ Và Tên
  • LEN(D2): Chiều dài của ô Tên
  • 1: Là chiều dài của một khoảng trắng

Từ đó, ta lập được công thức của Họ & tên đệm tại ô C2 sẽ là:

= LEFT(A2,LEN(A2) – LEN(D2) – 1)

Nhập công thức này vào ô C2, ta sẽ được Giá trị Họ & Tên Đệm

tach-phan-ho-va-ten-dem-trong-excel]

Kéo công thức xuống các ô bên dưới (Xem thêm bài về Fill Handle), ta được kết quả cho cả bảng số liệu như sau:

ket-qua-tach-ho-va-ten-trong-excel

Như vậy, ta đã có được kết quả từ việc tách họ và tên thành các cột riêng biệt trong Excel.

Bước cuối:  Bạn đã có được cột họ và tên riêng biệt. Bây giờ, tùy theo từng trường hợp, bạn có thể ẩn đi cột Họ và Tên, hoặc dùng Copy & dán giá trị 2 cột Họ, Tên  riêng biệt.

Chú ý là dán giá trị, chứ không phải dán bình thường, sau khi Copy bằng CTRL+C, ta làm như sau để dán giá trị:

  • Trên thanh Ribbon, chọn tab Home, click vào mũi tên của Paste rồi chọn Paste Special…
  • Trong hộp thoại Paste Special, click chọn Value, các tùy chọn khác mặc định, rồi click OK.

(Xem thêm hướng dẫn về Paste Special trong bài Chuyển Dòng Thành Cột Trong Excel)

Download file Excel ví dụ

Các bạn click vào link dưới để tải về máy file mẫu mình làm như trong bài này.

Link tải: http://www.trungtamtinhoc.edu.vn/download/tach-ho-va-ten.xlsx

Chúc các bạn thành công, SHARE nhé!

www.trungtamtinhoc.edu.vn




Click Xem thêm: , , , , , , ,

Đợi đã, hãy để lại email, chúng tôi sẽ gửi bài vào email bạn, cùng nhiều bài khác nữa.