Trong một số trường hợp, bạn phải chia ô trong Excel.Đó có thể là bạn lấy dữ liệu từ cơ sở dữ liệu, sao chép dữ liệu từ Internet hoặc lấy dữ liệu từ đồng nghiệp.
Một ví dụ đơn giản mà bạn cần chia ô trong Excel là khi bạn có đầy đủ tên và muốn tách chúng thành họ và tên.
Hoặc bạn nhận được địa chỉ 'và bạn muốn chia nhỏ địa chỉ để có thể phân tích thành phố hoặc mật khẩu riêng biệt.
Nội dung
Cách tách các ô trong Excel
Trong hướng dẫn này, bạn sẽ học cách tách ô trong Excel bằng các kỹ thuật sau:
- Sử dụng tính năng Văn bản thành Cột.
- Sử dụng các hàm văn bản Excel.
- Sử dụng Flash Fill (có sẵn trong năm 2013 và 2016).
chúng ta hãy bắt đầu!
Tách các ô trong Excel với văn bản thành cột
Dưới đây, tôi đã liệt kê tên của một số nhân vật hư cấu yêu thích của tôi mà tôi muốn tách thành các ô riêng biệt. :
Dưới đây là các bước để tách những tên này thành họ và tên:
- Chọn ô chứa văn bản bạn muốn tách (trong trường hợp này là A2: A7).
- Nhấp vào tab Dữ liệu
- Trong nhóm Công cụ dữ liệu, hãy bấm Văn bản thành Cột.
- Trong trình hướng dẫn Chuyển Văn bản thành Cột:
Thao tác này sẽ ngay lập tức chia văn bản của ô thành hai cột khác nhau.
ghi chú:
- Tính năng Văn bản thành Cột chia tách nội dung của các ô dựa trên các dấu phân cách.Mặc dù điều này hoạt động tốt nếu bạn muốn tách biệt họ và tên, trong trường hợp họ, tên đệm và họ, nó sẽ chia nó thành ba phần.
- Kết quả nhận được bằng cách sử dụng chức năng Văn bản thành Cột là tĩnh.Điều này có nghĩa là nếu có bất kỳ thay đổi nào trong dữ liệu gốc, bạn sẽ phải lặp lại quy trình để nhận được kết quả cập nhật.
Tách các ô trong Excel bằng các hàm văn bản
Các hàm văn bản trong Excel rất hữu ích khi bạn muốn cắt và cắt các chuỗi văn bản.
Trong khi hàm Văn bản thành Cột cung cấp kết quả tĩnh, kết quả thu được bằng cách sử dụng hàm là động và tự động cập nhật khi bạn thay đổi dữ liệu gốc.
Tách tên bằng họ và tên
Giả sử bạn có cùng một dữ liệu như sau:
trích xuất tên
Để lấy tên từ danh sách này, hãy sử dụng công thức sau:
= LEFT (A2, TÌM KIẾM ("", A2) -1)
Công thức này sẽ tìm ký tự khoảng trắng đầu tiên và sau đó trả về tất cả văn bản trước ký tự khoảng trắng đó:
Công thức này sử dụng hàm TÌM KIẾM để lấy vị trí của ký tự khoảng trắng.Trong ví dụ của Bruce Wayne, nhân vật không gian đứng ở vị trí thứ 6.Sau đó, nó sử dụng hàm LEFT để trích xuất tất cả các ký tự ở bên trái của nó.
Trích xuất họ
Tương tự như vậy, để lấy họ, hãy sử dụng công thức sau:
= RIGHT (A2, LEN (A2) -SEARCH ("", A2))
Công thức này sử dụng hàm TÌM KIẾM để tìm vị trí của phím cách bằng cách sử dụng hàm TÌM KIẾM.Sau đó, nó trừ số này khỏi tổng độ dài của tên (được cung cấp bởi hàm LEN).Điều này cho biết số ký tự trong họ.
Sau đó sử dụng chức năng RIGHT để trích xuất họ này.
Lưu ý:Các hàm này có thể không hoạt động chính xác nếu có khoảng trắng ở đầu, cuối hoặc kép trong tên.Nhấp vào đây để tìm hiểu cách xóa khoảng trắng đầu / cuối / kép trong Excel.
Tách tên bằng họ, tên đệm và họ
Trong một số trường hợp, bạn có thể kết hợp nhiều tên với nhau, một số còn có tên đệm.
Công thức trong trường hợp này hơi phức tạp.
trích xuất tên
Để lấy tên:
= LEFT (A2, TÌM KIẾM ("", A2) -1)
Đây là công thức tương tự mà chúng tôi sử dụng mà không có tên đệm.Nó chỉ tìm kiếm ký tự khoảng trắng đầu tiên và trả về tất cả các ký tự trước khoảng trắng.
trích xuất tên đệm
Để lấy tên đệm:
= IFERROR (MID (A2, SEARCH ("", A2) + 1, SEARCH ("", A2, SEARCH ("", A2) +1) -SEARCH ("", A2)), "")
Hàm MID bắt đầu với ký tự khoảng trắng đầu tiên và sử dụng sự khác biệt về vị trí của ký tự khoảng trắng đầu tiên và thứ hai để trích xuất tên đệm.
Nếu không có tên đệm, hàm MID trả về lỗi.Để tránh lỗi, nó được bao bọc trong hàm IFERROR.
Trích xuất họ
Để lấy họ, hãy sử dụng công thức sau:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2) -SEARCH(" ",A2,SEARCH(" ",A2)+1)))
Công thức này kiểm tra tên đệm (bằng cách đếm số ký tự khoảng trắng).Nếu chỉ có 1 ký tự khoảng trắng, nó chỉ trả về tất cả văn bản ở bên phải ký tự khoảng trắng.
Nhưng nếu có 2, thì nó tìm ký tự khoảng trắng thứ hai và trả về số ký tự sau khoảng trắng thứ hai.
Lưu ý: Các công thức này hoạt động tốt khi bạn chỉ có họ và tên hoặc họ, chữ đệm và tên.Tuy nhiên, nếu bạn có sự kết hợp của các hậu tố hoặc lời chào, thì bạn sẽ phải sửa đổi công thức thêm.
Tách các ô trong Excel bằng QuickFill
Flash Fill là một tính năng mới được giới thiệu trong Excel 2013.
Nó có thể rất tiện dụng khi bạn có một mẫu và muốn nhanh chóng trích xuất các phần của nó.
Ví dụ: hãy lấy dữ liệu họ và tên:
Quick Fill hoạt động bằng cách nhận dạng một mẫu và sao chép nó vào tất cả các ô khác.
Đây là cách sử dụng Flash Fill để trích xuất tên từ danh sách:
Flash Fill hoạt động như thế nào?
Flash Fill tìm một mẫu trong tập dữ liệu và sao chép nó.
Flash Fill là một tính năng thông minh đáng ngạc nhiên, hoạt động tốt trong hầu hết các trường hợp.Nhưng nó cũng không thành công trong một số trường hợp.
Ví dụ: nếu tôi có một danh sách tên với các tổ hợp tên, một số có tên đệm và những tên khác thì không.
Nếu tôi trích xuất tên đệm trong trường hợp này, Flash Fill sẽ trả về họ không chính xác trong trường hợp không có tên.
Thành thật mà nói, đây vẫn là một xu hướng gần đúng tốt.Tuy nhiên, đây không phải là điều tôi muốn.
Nhưng nó vẫn là một công cụ đủ tốt để giữ trong kho vũ khí của bạn và sử dụng khi cần thiết.
Đây là một ví dụ khác về điền nhanh hoạt động tốt.
Tôi có một tập hợp các địa chỉ mà tôi muốn nhanh chóng trích xuất các thành phố.
Để nhanh chóng nhận được thành phố, hãy nhập tên thành phố của địa chỉ đầu tiên (trong ví dụ này là London trong ô B2) và sử dụng tính năng tự động điền để điền vào tất cả các ô.Bây giờ hãy sử dụng Flash Fill và nó sẽ ngay lập tức cung cấp cho bạn tên thành phố cho từng địa chỉ.
Tương tự như vậy, bạn có thể tách địa chỉ và trích xuất bất kỳ phần nào của địa chỉ.
Lưu ý rằng điều này yêu cầu các địa chỉ phải là một tập dữ liệu đồng nhất với cùng một dấu phân cách (dấu phẩy trong trường hợp này).
Nếu bạn cố gắng sử dụng Flash Fill mà không có mẫu, nó sẽ hiển thị lỗi như sau:
Trong hướng dẫn này, tôi đã trình bày ba cách khác nhau để chia ô trong Excel thành nhiều cột (sử dụng Văn bản thành Cột, Công thức và Điền nhanh)
Tôi hy vọng bạn thấy hướng dẫn Excel này hữu ích.
