Bạn có thể sử dụng Excel để chia nhỏ thông tin thành nhiều phần nhỏ hơn. Tìm kiếm dữ liệu bạn cần và thao tác với nó là một mục tiêu quan trọng đối với nhiều người dùng Excel.
Nếu bạn có tên đầy đủ của một người, bạn có thể chỉ cần điền tên hoặc họ của họ. Ví dụ: nếu bạn đang gửi một email tự động thân thiện cho khách hàng của mình, bạn cần sử dụng tên của họ để tránh nghe có vẻ mạo danh. Nếu bạn đang xem qua danh sách những người trả lời cuộc thăm dò, điều quan trọng là chỉ sử dụng họ của họ hoặc ẩn họ của họ để duy trì tình trạng ẩn danh.
Excel làm cho quá trình này trở nên đơn giản và có nhiều cách tiếp cận khác nhau mà bạn có thể thực hiện. Đây là một hướng dẫn sẽ giúp bạn tạo các cột Họ và Tên riêng biệt bằng cách sử dụng công thức. Chúng tôi cũng đề cập đến vấn đề tên đệm.
Công thức Excel để tách tên thành các phần
Bạn bắt đầu từ đâu?
Tách biệt tên
Đây là công thức chung:
= LEFT (ô, TÌM (, ô, 1) -1)
Để thực thi nó, hãy thay thế ô với con trỏ ô có chứa tên đầy đủ đầu tiên mà bạn muốn tách. Trong ví dụ này, bạn muốn chọn B2 và nhập công thức:
= LEFT (A2, FIND (, A2,1) -1)
Tuy nhiên, điều quan trọng cần lưu ý là trên một số thiết bị, công thức này sử dụng dấu chấm phẩy thay vì dấu phẩy. Vì vậy, nếu công thức trên không phù hợp với bạn, bạn có thể cần sử dụng phiên bản sau để thay thế:
= LEFT (ô; TÌM (; ô; 1) -1)
Trong ví dụ, bạn sẽ sử dụng:
= LEFT (A2; FIND (; A2; 1) -1)
Bây giờ bạn có thể chỉ cần kéo chốt điền xuống cuối cột Tên.
Hàm LEFT cho phép bạn tách một chuỗi, bắt đầu từ phần cuối bên trái của văn bản. Phần TÌM của công thức này định vị khoảng trống đầu tiên trong tên đầy đủ, vì vậy bạn sẽ nhận được phần tên đầy đủ của mình đứng trước khoảng trống.
Do đó, các tên có dấu gạch nối ở cùng nhau và các tên có chứa các ký tự đặc biệt cũng vậy. Nhưng cột Tên đầy đủ của bạn sẽ không chứa tên đệm hoặc tên viết tắt.
Dấu phẩy hay Dấu chấm phẩy?
Tại sao công thức không giống nhau cho tất cả mọi người?
Đối với nhiều người dùng Excel, các hàm Excel sử dụng dấu phẩy để phân tách dữ liệu đầu vào. Nhưng trên một số thiết bị, cài đặt khu vực khác nhau.
Để khám phá biểu tượng nào được Excel của bạn sử dụng, chỉ cần bắt đầu nhập công thức. Khi bạn bắt đầu nhập = LEFT ( , bạn sẽ thấy một văn bản di chuột sẽ đề xuất định dạng phù hợp.
Tách họ
Thực hiện cách tiếp cận tương tự để tách họ. Lần này, bạn nên sử dụng công thức RIGHT, phân tách các chuỗi bắt đầu từ phía bên phải.
Công thức bạn cần là:
= RIGHT (ô, LEN (ô) - SEARCH (#, SUBSTITUTE (ô, #, LEN (ô) - LEN (SUBSTITUTE (ô,)))))
Trong ví dụ trên, bạn sẽ sử dụng công thức sau trong ô C2:
= RIGHT (A2, LEN (A2) - SEARCH (#, SUBSTITUTE (A2 ,, #, LEN (A2) - LEN (SUBSTITUTE (A2 ,,)))))
Một lần nữa, bạn có thể cần chuyển từ dấu phẩy sang dấu chấm phẩy, nghĩa là bạn có thể cần sử dụng:
= RIGHT (A2; LEN (A2) - SEARCH (#; SUBSTITUTE (A2 ;; #; LEN (A2) - LEN (SUBSTITUTE (A2 ;;)))))
Họ được gạch nối và họ với các ký tự đặc biệt vẫn được giữ nguyên.
Tại sao công thức này phức tạp hơn công thức dành cho tên riêng? Việc tách tên đệm và tên viết tắt ra khỏi họ sẽ khó hơn.
Nếu bạn muốn tên đệm và tên viết tắt được liệt kê cùng với họ, bạn có thể sử dụng công thức:
= RIGHT (ô, LEN (ô) - TÌM KIẾM (, ô))
hoặc là:
= RIGHT (A2, LEN (A2) - TÌM KIẾM (, A2))
hoặc là:
= RIGHT (A2; LEN (A2) - TÌM KIẾM (; A2))
Nhưng nếu bạn muốn tách các tên đệm ra thì sao? Điều này ít phổ biến hơn nhưng có thể hữu ích nếu bạn biết.
Tách tên đệm
Công thức cho tên đệm như sau:
làm thế nào để loại bỏ văn bản trong sự bất hòa
= MID (ô, TÌM KIẾM (, ô) + 1, TÌM KIẾM (, ô, TÌM KIẾM (, ô) +1) - TÌM KIẾM (, ô) -1)
Trong ví dụ trên, bạn nhận được:
= MID (A2, TÌM KIẾM (, A2) + 1, TÌM KIẾM (, A2, TÌM KIẾM (, A2) +1) - TÌM KIẾM (, A2) -1)
Nếu Excel của bạn sử dụng dấu chấm phẩy, công thức là:
= MID (A2; TÌM KIẾM (; A2) + 1; TÌM KIẾM (; A2; TÌM KIẾM (; A2) +1) - TÌM KIẾM (; A2) -1)
Sau khi nhập công thức, hãy kéo chốt điền xuống. Đây là cột Tên đệm được thêm vào ví dụ trên:
Nếu tên đầy đủ không chứa tên đệm hoặc tên viết tắt, bạn sẽ nhận được các giá trị 0 trong cột này, có thể được hiển thị là #VALUE !. Để lấy các ô trống thay cho #VALUE !, bạn có thể sử dụng hàm IFERROR.
Sau đó, công thức của bạn trở thành:
= IFERROR (MID (ô, TÌM KIẾM (, ô) + 1, TÌM KIẾM (, ô, TÌM KIẾM (, ô) +1) - TÌM KIẾM (, ô) -1), 0)
hoặc là:
= IFERROR (MID (A2, TÌM KIẾM (, A2) + 1, TÌM KIẾM (, A2, TÌM KIẾM (, A2) +1) - TÌM KIẾM (, A2) -1), 0)
hoặc là:
= IFERROR (MID (A2; TÌM KIẾM (; A2) + 1; TÌM KIẾM (; A2; TÌM KIẾM (; A2) +1) - TÌM KIẾM (; A2) -1); 0)
Một cách tiếp cận để tách nhiều tên đệm
Điều gì xảy ra nếu ai đó trong danh sách của bạn có nhiều tên đệm? Sử dụng công thức trên, chỉ tên đệm đầu tiên của họ sẽ được truy xuất.
Để giải quyết vấn đề này, bạn có thể thử một cách tiếp cận khác để tách tên đệm. Nếu bạn đã tạo các cột tên và họ, bạn có thể đơn giản cắt bỏ chúng. Mọi thứ còn lại sẽ được tính là tên đệm.
Công thức này là:
= TRIM (MID (ô1, LEN (ô2) + 1, LEN (ô1) -LEN (ô2 & ô3)))
Ở đây, ô1 đề cập đến con trỏ ô dưới cột Họ và Tên, ô2 đề cập đến con trỏ ô dưới cột Tên, trong khi ô 3 đề cập đến con trỏ ô dưới cột Họ. Trong ví dụ trên, chúng tôi nhận được:
= TRIM (MID (A2, LEN (B2) + 1, LEN (A2) -LEN (B2 & D2))))
hoặc là:
= TRIM (MID (A2; LEN (B2) +1; LEN (A2) -LEN (B2 & D2)))
Nếu bạn sử dụng công thức này, bạn sẽ không phải lo lắng về các giá trị bằng không.
Tóm tắt nhanh
Dưới đây là các công thức mà bạn có thể sử dụng để tách tên đầy đủ thành các phần:
Tên: = LEFT (ô, TÌM (, ô, 1) -1)
Họ: = RIGHT (ô, LEN (ô) - SEARCH (#, SUBSTITUTE (ô, #, LEN (ô) - LEN (SUBSTITUTE (ô,)))))
Tên đệm: = IFERROR (MID (ô, TÌM KIẾM (, ô) + 1, TÌM KIẾM (, ô, TÌM KIẾM (, ô) +1) - TÌM KIẾM (, ô) -1), 0)
Công thức thay thế cho tên đệm: = TRIM (MID (ô1, LEN (ô2) + 1, LEN (ô1) -LEN (ô2 & ô3)))
Tách biệt Tên và Họ mà không cần Sử dụng Công thức
Nếu bạn cảm thấy không muốn nhập một loạt công thức có thể bị nhập sai, thì hãy tận dụng Trình hướng dẫn Chuyển văn bản thành Cột tích hợp của Excel.
- Hãy đảm bảo rằng Dữ liệu tab được chọn từ menu ở trên cùng và đánh dấu cột bạn muốn chuyển đổi.
- Sau đó, nhấp vào Văn bản thành cột .
- Tiếp theo, hãy đảm bảo Được phân định được chọn và nhấp vào Kế tiếp
. - Bây giờ, hãy chọn Không gian từ các tùy chọn và nhấp vào Kế tiếp .
- Sau đó, thay đổi Nơi Đến đến$ B $ 2và bấm vào Hoàn thành.
Kết quả cuối cùng sẽ như thế này.
.
Kết quả cuối cùng sẽ như thế này.
Lời cuối cùng
Có nhiều cách khác để giải quyết vấn đề này trong Excel. Nếu không có tùy chọn có sẵn nào đáp ứng được những gì bạn cần, hãy thực hiện thêm một số nghiên cứu.
Sử dụng công thức tương đối đơn giản và nó không phụ thuộc vào phiên bản Excel bạn đang sử dụng. Nhưng thật không may, bạn vẫn có thể gặp lỗi.
Ví dụ: nếu tên đầy đủ của một người bắt đầu bằng họ của họ, tên này sẽ bị tách ra theo cách khác nhau. Các công thức cũng sẽ gặp rắc rối với họ có chứa tiền tố hoặc hậu tố, chẳng hạn như le Carré hoặc van Gogh. Nếu tên của ai đó kết thúc bằng Jr., tên đó sẽ được liệt kê là họ của họ.
Tuy nhiên, có những sửa đổi bạn có thể thêm để giải quyết những vấn đề này khi chúng xuất hiện. Làm việc với các công thức mang lại cho bạn sự linh hoạt cần thiết để giải quyết những phức tạp này.
