Làm cách nào để trích xuất tên và họ trong excel?

Đã có vô số lần tôi có một danh sách tên đầy đủ và tất cả những gì tôi cần là Tên. Sẽ rất tốn thời gian để gọi tên từng người một

Cảm ơn chúa, có các công thức để Excel trích xuất tên từ tên đầy đủ để giúp cuộc sống của tôi dễ dàng hơn

Trong bài viết này, các bạn sẽ được hướng dẫn chi tiết cách tách họ tên trong Excel

  • Sử dụng công thức Tìm & Trái
  • Sử dụng tính năng Text to Column

Hãy cùng khám phá từng phương pháp này để trích xuất tên từ tên đầy đủ trong Excel từng cái một.

 

Sử dụng công thức Tìm và Trái

Rất dễ dàng để trích xuất tên trong Excel bằng công thức TRÁI và TÌM

Làm cách nào để trích xuất tên và họ trong excel?

Đây là kế hoạch trò chơi

  • Sử dụng công thức TÌM để tìm vị trí của khoảng trắng hoặc bất kỳ dấu phân cách nào khác ngăn cách Tên và Họ
  • Tuy nhiên, chúng ta cần trừ vị trí số này đi 1 để chúng ta có vị trí của phần cuối của Tên
  • Với số này, chúng tôi sẽ sử dụng công thức LEFT để truy xuất Tên

Bây giờ, điều cần thiết là phải hiểu cách thức hoạt động của các hàm này để có thể sử dụng chúng để trích xuất tên trong Excel

 

Công thức FIND được sử dụng để tìm vị trí của văn bản được chỉ định trong một ô

Cú pháp của công thức FIND là

=FIND(tìm_văn_bản, trong_văn_bản, [số_bắt_đầu])

  • find_text. Đó là văn bản bạn muốn tìm. Ở đây, chúng tôi muốn tìm không gian. Vì vậy, đối số sẽ để trống trong dấu ngoặc kép
  • trong_text. Đó là ô chứa văn bản bạn muốn tìm
  • start_num. Nó chỉ định ký tự mà bạn muốn bắt đầu tìm kiếm. Đây là một lập luận tối ưu. Nếu để trống, Excel sẽ bắt đầu tìm kiếm từ chính ký tự đầu tiên

 

Công thức LEFT dùng để trích xuất số lượng ký tự đã chỉ định từ đầu chuỗi văn bản

Cú pháp của công thức TEXT là

=LEFT(văn bản, num_chars)

  • chữ. Đó là chuỗi văn bản mà bạn muốn trích xuất các ký tự
  • num_chars. Đó là số lượng ký tự bạn muốn trích xuất

 

Tiếp tục, hãy kết hợp hai công thức này để Excel trích xuất tên từ tên đầy đủ

Xem nó trên YouTube và cho nó một ngón tay cái lên



Làm cách nào để trích xuất tên và họ trong excel?

Thực hiện theo hướng dẫn từng bước bên dưới về cách lấy tên trong Excel và đảm bảo tải xuống sổ làm việc Excel để làm theo

DOWNLOAD EXCEL WORKBOOK

BƯỚC 1. Chúng ta cần nhập hàm LEFT và chọn Họ và tên.

=TRÁI(C7

Làm cách nào để trích xuất tên và họ trong excel?

 

BƯỚC 2. Ta cần nhập công thức FIND để lấy khoảng trống giữa họ và tên.

=LEFT(C7, FIND(” “

Làm cách nào để trích xuất tên và họ trong excel?

 

BƯỚC 3. Chọn lại Tên đầy đủ cho đối số thứ 2 của công thức TÌM.

=LEFT(C7, FIND(” “, C7)

Làm cách nào để trích xuất tên và họ trong excel?

 

BƯỚC 4.  Trừ 1 từ công thức TÌM để kết quả của chúng tôi sẽ trả về cho chúng tôi văn bản có chữ cái cuối cùng của tên.

=LEFT(C7, FIND(” “, C7) -1)

Làm cách nào để trích xuất tên và họ trong excel?

 

BƯỚC 5. Làm tương tự cho các ô còn lại bằng cách kéo công thức xuống hết cỡ bằng nút chuột trái.

 

Làm cách nào để trích xuất tên và họ trong excel?

Giờ đây, bạn có thể trích xuất tất cả Tên từ TÊN ĐẦY ĐỦ của mình bằng cách sử dụng công thức TÌM & TRÁI trong Excel

 

Sử dụng tính năng Text to Column

Một cách nhanh chóng và dễ dàng hơn để Excel trích xuất tên là sử dụng chức năng Text to Column

Tất cả những gì bạn cần biết trước tiên là dấu phân cách giữa họ và tên. Ở đây, họ và tên cách nhau bởi dấu COMMA (,)

Làm cách nào để trích xuất tên và họ trong excel?

 

Hàm này sẽ lấy họ và tên tách thành 2 cột riêng biệt. Hoàn thành việc này bằng cách thực hiện theo các bước bên dưới

Mặc dù hướng dẫn này là về trường Tên, vấn đề tương tự có thể xảy ra ở nơi khác. Thật dễ dàng để tạo bảng tính mà không cần suy nghĩ về cách dữ liệu sẽ được sử dụng. Tôi đã phạm tội này bản thân mình. Dưới đây là một số ví dụ mà tôi đã thấy khi có quá nhiều dữ liệu trong một cột và cần được chia nhỏ hoặc phân tích cú pháp

  • địa chỉ
  • sinh nhật
  • Số điện thoại

Có quá nhiều dữ liệu trong một trường có thể gây ra sự cố. Trong trường hợp của bạn tôi, đó là cá nhân hóa. Những lần khác, quá nhiều dữ liệu có thể gây ra sự cố sắp xếp và lọc

Chìa khóa để giải quyết vấn đề này là phân tích cú pháp hoặc phân tách dữ liệu dựa trên khớp mẫu hoặc chuỗi con Excel. Các dấu phân cách điển hình bao gồm tab, dấu chấm phẩy, dấu phẩy, dấu gạch ngang và dấu cách. Ví dụ: nếu bạn chuyển đến tab Dữ liệu, bạn sẽ thấy mục Văn bản thành Cột. Thật không may, có vẻ như mọi người bỏ qua lựa chọn này vì dữ liệu của họ đã có sẵn trong các cột

Trước khi chúng tôi bắt đầu, có thể có một cách dễ dàng để phân tích các ô của bạn bằng tính năng điền flash của Excel. Nó cung cấp khả năng phân tích cú pháp dễ dàng khi bạn có các trường nhất quán. Giải pháp này không yêu cầu công thức, nhưng khớp mẫu. Và vào cuối năm 2022, Microsoft sẽ phát hành thêm các chức năng văn bản cho người dùng Microsoft 365 bao gồm TEXTBEFORE, TEXTAFTER và TEXTSPLIT

Cách tách Họ và Tên trong Excel

  1. Nhấp chuột phải vào tiêu đề cột ở bên phải tên bạn muốn tách và chọn Chèn. Ví dụ: nếu tên của bạn ở Cột A, hãy nhấp vào Cột B. Cột trống mới này là nơi chúng tôi sẽ đặt Họ
Làm cách nào để trích xuất tên và họ trong excel?
Làm cách nào để trích xuất tên và họ trong excel?
Tên đầy đủ để phân tích
  1. Nhấp vào tiêu đề cột của cột bạn muốn tách. Trong ví dụ của tôi, các tên nằm trong Cột A
  2. Từ menu Dữ liệu, chọn Văn bản thành Cột. Trình hướng dẫn dữ liệu xuất hiện
Làm cách nào để trích xuất tên và họ trong excel?
Làm cách nào để trích xuất tên và họ trong excel?
Trình hướng dẫn chuyển đổi văn bản thành cột trong Excel
  1. Chọn nút radio Được phân tách trong phần Kiểu dữ liệu gốc
  2. Nhấn tiếp
  3. Tại phần Dấu phân cách, chọn mục phân tách dữ liệu của bạn. Trong ví dụ của tôi, một dấu cách ngăn cách Họ và Tên
  4. Nhìn vào phần Xem trước dữ liệu. Nếu dữ liệu có vẻ chính xác, hãy nhấp vào Tiếp theo
Làm cách nào để trích xuất tên và họ trong excel?
Làm cách nào để trích xuất tên và họ trong excel?
Xem trước Tên & Họ trong một cột mới
  1. Nhấp vào cột đầu tiên trong phần Xem trước dữ liệu và chọn loại định dạng dữ liệu Cột. Tôi đã giữ phạm vi Đích mặc định
Làm cách nào để trích xuất tên và họ trong excel?
Làm cách nào để trích xuất tên và họ trong excel?
Chỉ định định dạng dữ liệu và đích
  1. Nhấp vào Kết thúc
  2. Gắn nhãn cột mới của bạn
Làm cách nào để trích xuất tên và họ trong excel?
Làm cách nào để trích xuất tên và họ trong excel?
Tách Họ và Tên trong Excel

Tên đệm và tên viết tắt ngoại lệ

Không lâu sau khi tôi giải quyết vấn đề của bạn mình, anh ấy đã gọi cho tôi để nói rằng giải pháp của tôi không còn hiệu quả nữa. Sau khi đặt một số câu hỏi, một tỷ lệ nhỏ người đặt tên đệm hoặc chữ cái đầu tiên của họ. Tôi nhắc anh ấy rằng giải pháp trước đó đã cung cấp cho anh ấy “tên gọi” cần thiết của anh ấy. ” Sau đó, anh ấy nói với tôi rằng anh ấy cũng đang sử dụng trường “họ” hoặc họ. Vì vậy, việc để phần giữa làm mọi thứ rối tung lên và thuật sĩ Chuyển văn bản thành Cột không phải là giải pháp tốt nhất

Một giải pháp tốt hơn là sử dụng các công thức Excel hoạt động hơi giống với trình hướng dẫn ở chỗ chúng tôi đang tìm kiếm ô để tìm khoảng trắng. Mục tiêu là để có được một cái gì đó giống như ví dụ dưới đây

Làm cách nào để trích xuất tên và họ trong excel?
Làm cách nào để trích xuất tên và họ trong excel?
Tên được chia thành Tên, Tên đệm và Họ

Tìm kiếm TRÁI cho tên

Một điểm khác biệt trong việc sử dụng công thức Excel là tôi giữ nguyên trường Tên (Chủ tịch) ban đầu. Vì vậy, để có được Tên, tôi sẽ sử dụng một công thức để tìm kiếm trong nội dung ô Tổng thống

  1. Chèn một cột mới có tên First vào bên phải cột Name của bạn. Trong ví dụ của tôi, đây là cột B
  2. Trong ô B2 nhập =LEFT(A2,SEARCH(" ", A2) -1))
  3. Lấy núm điều khiển định cỡ ở góc dưới bên phải và kéo nó xuống để sao chép công thức sang các ô khác

Trong ví dụ này, tôi đang tìm kiếm phiên bản đầu tiên của khoảng trắng trong ô A2. Khi nó được tìm thấy, tôi di chuyển sang trái và lấy nội dung. Điều này hoạt động tốt trừ khi ai đó không có khoảng trắng, như Cher, đăng ký nhận bản tin của bạn tôi

Xử lý tên đệm

Tôi sẽ thừa nhận tôi nghĩ phần này sẽ dễ dàng hơn. Thật không may, tôi không có một công thức hoàn hảo vì những điều bất thường. Vâng, ngay cả Tổng thống Hoa Kỳ cũng có thể là ngoại lệ. Đó là một cách thú vị để tôi nói rằng nó không hoàn hảo và không xử lý được Martin Van Buren. Công thức đã phân tích cú pháp tên, nhưng tạo ra họ của anh ấy là Buren. Vì vậy, tôi đang thông báo cho bạn;

Bạn cũng sẽ nhận thấy rằng tôi đã sử dụng IFERROR trong công thức. Mặc dù không cần thiết nhưng tôi không thích nhìn thấy tất cả các mục nhập ô #VALUE cho những người không có tên đệm. Vì vậy, bằng cách sử dụng IFERROR , tôi có thể thay thế dấu cách ” ” thay vào đó

  1. Chèn một cột mới có tên Middle. Trong ví dụ của tôi, đây sẽ là cột C
  2. Trong ô C2 nhập =IFERROR(MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)), " ")
  3. Lấy núm điều khiển định cỡ ở góc dưới bên phải của ô C2 và kéo nó xuống để sao chép công thức

Phân tích họ

Thông thường, họ cũng có thể được xử lý theo công thức. Trong trường hợp này, tôi đã sử dụng công thức được đề cập bởi ExcelChamps

  1. Chèn một cột mới có tên là Họ hoặc Họ
  2. Nhập công thức =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
  3. Lấy núm điều khiển định cỡ ở góc dưới bên phải của ô D2 và kéo nó xuống để sao chép công thức

Tách tên bằng Flash Fill

Một cách khác để phân tích tên là sử dụng tính năng Excel có tên Flash Fill. Nó được sử dụng tốt nhất với các cột Tên và Họ. Tính năng này không dựa vào công thức hoặc tiện ích Chuyển văn bản thành cột. Nó dựa vào nhận dạng mẫu, nhưng bạn không phải xác định mẫu

Làm cách nào để trích xuất tên và họ trong excel?
Làm cách nào để trích xuất tên và họ trong excel?
Sử dụng Flash Fill để tách tên

Ý tưởng đằng sau Flash Fill là bạn đào tạo Excel bằng một vài mục ví dụ. Sau đó, dựa trên các mục nhập của bạn, Excel sẽ cố gắng điền vào các mục nhập còn lại. Trong một số trường hợp, bạn thậm chí không cần nhấn mục Flash Fill trên menu Data. Nó làm tôi nhớ một chút về danh sách tùy chỉnh của Excel, ngoại trừ việc bạn không phải xác định chúng

Như bạn có thể đoán, nó sẽ bị vấp ở các hậu tố. Xin lỗi Jr.

Trường hợp kỳ quặc

Danh sách của bạn vẫn có thể có những trường hợp kỳ quặc mà bạn có thể muốn sử dụng phần bổ trợ Microsoft Excel thương mại như công cụ AbleBits Split Names, là một phần của Ultimate Suite nếu bạn có nhiều phần bổ trợ ngoại lệ. Tôi không chắc liệu nó có thể xử lý các họ như Van Buren hoặc Van Gogh hay không, nhưng nó có thể giúp bạn với các hậu tố và tiền tố

Bảng tính mẫu có công thức

Một trong những điều mà tôi nhận ra là dù tôi có cẩn thận đến mức nào khi nhập công thức, thì sai sót vẫn xảy ra. Ví dụ: trong trang tính được tham chiếu bên dưới, tôi gặp lỗi vì một trong các trường của tôi có dấu cách ở cuối. Tôi cũng có lỗi khi bỏ dấu đóng ) trên một số công thức. Tin vui là file Excel mẫu của các Tổng thống Mỹ đã được phân tích tên và công thức