Chuyển văn bản thành cột là một tính năng tuyệt vời trong Excel xứng đáng nhận được nhiều tín dụng hơn mức bình thường
Đúng như tên gọi, nó được dùng để chia văn bản thành nhiều cột. Ví dụ: nếu bạn có tên và họ trong cùng một ô, thì bạn có thể sử dụng điều này để nhanh chóng tách chúng thành hai ô khác nhau
Điều này có thể thực sự hữu ích khi bạn lấy dữ liệu từ cơ sở dữ liệu hoặc bạn nhập dữ liệu đó từ các định dạng tệp khác như Văn bản hoặc CSV
Trong hướng dẫn này, bạn sẽ tìm hiểu về nhiều điều hữu ích có thể được thực hiện bằng cách sử dụng Văn bản thành Cột trong Excel
Hướng dẫn này bao gồm
- Nơi tìm văn bản thành cột trong Excel
- Ví dụ 1 – Tách Tên thành Tên và Họ
- Ví dụ 2 – Chia Id Email thành Tên người dùng và Tên miền
- Ví dụ 3 – Lấy tên miền gốc từ URL
- Ví dụ 4 – Chuyển đổi định dạng ngày không hợp lệ thành định dạng ngày hợp lệ
- Ví dụ 5 – Chuyển văn bản thành số
- Ví dụ 6 – Trích xuất 5 ký tự đầu tiên của chuỗi
- Ví dụ 7 – Chuyển đổi số có dấu trừ ở cuối thành số âm
Nơi tìm văn bản thành cột trong Excel
Để truy cập Văn bản thành Cột, hãy chọn tập dữ liệu và đi tới Dữ liệu → Công cụ dữ liệu → Văn bản thành Cột
Thao tác này sẽ mở Trình hướng dẫn Chuyển văn bản thành Cột
Trình hướng dẫn này có ba bước và lấy một số thông tin đầu vào của người dùng trước khi chia văn bản thành các cột [bạn sẽ thấy các tùy chọn khác nhau này có thể được sử dụng như thế nào trong các ví dụ bên dưới]
Để truy cập Văn bản thành Cột, bạn cũng có thể sử dụng phím tắt – ALT + A + E
Bây giờ, hãy đi sâu vào và xem một số nội dung thú vị mà bạn có thể thực hiện với Chuyển văn bản thành Cột trong Excel
Ví dụ 1 – Tách Tên thành Tên và Họ
Giả sử bạn có một tập dữ liệu như hình bên dưới
Để nhanh chóng tách tên và họ và lấy chúng trong các ô riêng biệt, hãy làm theo các bước dưới đây
- Chọn tập dữ liệu
- Chuyển đến Dữ liệu → Công cụ dữ liệu → Văn bản thành cột. Thao tác này sẽ mở Trình hướng dẫn Chuyển văn bản thành Cột
- Ở Bước 1, đảm bảo đã chọn Phân cách [cũng là lựa chọn mặc định]. Nhấp vào Tiếp theo
- Ở Bước 2, chọn 'Dấu cách' làm dấu phân cách. Nếu bạn nghi ngờ rằng có thể có khoảng cách gấp đôi/ba lần liên tiếp giữa các tên, hãy chọn tùy chọn 'Coi các dấu phân cách liên tiếp là một'. Nhấp vào Tiếp theo
- Ở Bước 3, chọn ô đích. Nếu bạn không chọn một ô đích, nó sẽ ghi đè lên tập dữ liệu hiện có của bạn bằng tên trong cột đầu tiên và họ trong cột liền kề. Nếu bạn muốn giữ nguyên dữ liệu gốc, hãy tạo một bản sao hoặc chọn một ô đích khác
- Nhấp vào Kết thúc
Điều này sẽ ngay lập tức cung cấp cho bạn kết quả với tên trong một cột và họ trong cột khác
Ghi chú
- Kỹ thuật này hoạt động tốt khi tên của bạn chỉ bao gồm tên và họ. Trong trường hợp có tên viết tắt hoặc tên đệm, thì điều này có thể không hoạt động. Nhấp vào đây để xem hướng dẫn chi tiết về cách xử lý các trường hợp có nhiều cách kết hợp tên khác nhau
- Kết quả bạn nhận được từ việc sử dụng tính 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
Also read: How to Sort by the Last Name in Excel
Ví dụ 2 – Chia Id Email thành Tên người dùng và Tên miền
Văn bản thành Cột cho phép bạn chọn dấu phân cách của riêng mình để chia văn bản
Điều này có thể được sử dụng để chia địa chỉ email thành tên người dùng và tên miền vì chúng được phân tách bằng dấu @
Giả sử bạn có một tập dữ liệu như hình bên dưới
Đây là một số id email hư cấu của một số siêu anh hùng tuyệt vời [ngoại trừ bản thân tôi, tôi chỉ là một anh chàng thường xuyên lãng phí thời gian trên Netflix]
Dưới đây là các bước để tách các tên người dùng và tên miền này bằng tính năng Văn bản thành Cột
- Chọn tập dữ liệu
- Chuyển đến Dữ liệu → Công cụ dữ liệu → Văn bản thành cột. Thao tác này sẽ mở Trình hướng dẫn Chuyển văn bản thành Cột
- Ở Bước 1, đảm bảo đã chọn Phân cách [cũng là lựa chọn mặc định]. Nhấp vào Tiếp theo
- Ở Bước 2, chọn Khác và nhập @ vào ô bên phải. Đảm bảo bỏ chọn bất kỳ tùy chọn nào khác [nếu được chọn]. Nhấp vào Tiếp theo
- Thay đổi ô đích thành ô bạn muốn có kết quả
- Nhấp vào Kết thúc
Điều này sẽ chia địa chỉ email và cung cấp cho bạn tên và họ trong các ô riêng biệt
Ví dụ 3 – Lấy tên miền gốc từ URL
Nếu bạn làm việc với các URL web, đôi khi bạn có thể cần biết tổng số tên miền gốc duy nhất
Ví dụ: trong trường hợp http. //www. Google. com/example1 và http. //Google. com/example2 , tên miền gốc giống nhau, đó là www. Google. com
Giả sử bạn có một tập dữ liệu như hình bên dưới
Dưới đây là các bước để lấy tên miền gốc từ các URL này
- Chọn tập dữ liệu
- Chuyển đến Dữ liệu → Công cụ dữ liệu → Văn bản thành cột. Thao tác này sẽ mở Trình hướng dẫn Chuyển văn bản thành Cột
- Ở Bước 1, đảm bảo đã chọn Phân cách [cũng là lựa chọn mặc định]. Nhấp vào Tiếp theo
- Ở Bước 2, chọn Khác và nhập / [dấu gạch chéo lên] vào ô bên phải. Đảm bảo bỏ chọn bất kỳ tùy chọn nào khác [nếu được chọn]. Nhấp vào Tiếp theo
- Thay đổi ô đích thành ô bạn muốn có kết quả
- Nhấp vào Kết thúc
Điều này sẽ phân tách URL và cung cấp cho bạn tên miền gốc [trong cột thứ ba vì có hai dấu gạch chéo lên trước nó]
Bây giờ nếu bạn muốn tìm số lượng tên miền duy nhất, chỉ cần loại bỏ các tên trùng lặp
Ghi chú. Điều này hoạt động tốt khi bạn có tất cả các URL có http. // ở thời điểm bắt đầu. Nếu không, thì bạn sẽ nhận được tên miền gốc trong chính cột đầu tiên. Một phương pháp hay là làm cho các URL này nhất quán trước khi sử dụng Văn bản thành Cột
Ví dụ 4 – Chuyển đổi định dạng ngày không hợp lệ thành định dạng ngày hợp lệ
Nếu bạn lấy dữ liệu của mình từ các cơ sở dữ liệu như SAP/Oracle/Capital IQ hoặc bạn nhập dữ liệu đó từ một tệp văn bản, thì có khả năng định dạng ngày tháng không chính xác [i. e. , một định dạng mà Excel không coi là ngày tháng]
Chỉ có một vài định dạng mà Excel có thể hiểu được và bất kỳ định dạng nào khác cần được chuyển đổi thành định dạng hợp lệ để sử dụng trong Excel
Giả sử bạn có ngày ở định dạng dưới đây [không phải ở định dạng ngày Excel hợp lệ]
Dưới đây là các bước để chuyển đổi chúng thành định dạng ngày hợp lệ
- Chọn tập dữ liệu
- Chuyển đến Dữ liệu → Công cụ dữ liệu → Văn bản thành cột. Thao tác này sẽ mở Trình hướng dẫn Chuyển văn bản thành Cột
- Ở Bước 1, đảm bảo đã chọn Phân cách [cũng là lựa chọn mặc định]. Nhấp vào Tiếp theo
- Ở Bước 2, đảm bảo KHÔNG chọn tùy chọn dấu phân cách. Nhấp vào Tiếp theo
- Trong Định dạng dữ liệu cột, chọn Ngày và chọn định dạng bạn muốn [DMY có nghĩa là ngày tháng năm]. Ngoài ra, hãy thay đổi ô đích thành ô bạn muốn có kết quả
- Nhấp vào Kết thúc
Thao tác này sẽ ngay lập tức chuyển đổi các định dạng ngày không hợp lệ này thành các định dạng ngày hợp lệ mà bạn có thể sử dụng trong Excel
Ví dụ 5 – Chuyển văn bản thành số
Đôi khi bạn nhập dữ liệu từ cơ sở dữ liệu hoặc các định dạng tệp khác, các số được chuyển thành định dạng văn bản
Có một số cách điều này có thể xảy ra
- Có dấu nháy đơn trước số. Điều này dẫn đến số được coi là văn bản
- Lấy số do các hàm văn bản như LEFT, RIGHT hoặc MID
Vấn đề với điều này là những số này [ở định dạng văn bản] bị bỏ qua bởi các hàm Excel, chẳng hạn như SUM và AVERAGE
Giả sử bạn có tập dữ liệu như hình bên dưới với các số ở định dạng văn bản [lưu ý rằng các số này được căn sang trái]
Sau đây là các bước sử dụng Text to Columns để chuyển văn bản thành số
- Chọn tập dữ liệu
- Chuyển đến Dữ liệu → Công cụ dữ liệu → Văn bản thành cột. Thao tác này sẽ mở Trình hướng dẫn Chuyển văn bản thành Cột
- Ở Bước 1, đảm bảo đã chọn Phân cách [cũng là lựa chọn mặc định]. Nhấp vào Tiếp theo
- Ở Bước 2, đảm bảo KHÔNG chọn tùy chọn dấu phân cách. Nhấp vào Tiếp theo
- Trong Định dạng dữ liệu cột, chọn Chung. Ngoài ra, hãy thay đổi ô đích thành ô bạn muốn có kết quả
- Nhấp vào Kết thúc
Thao tác này sẽ chuyển đổi những số này trở lại định dạng Chung mà hiện có thể được sử dụng trong các công thức
Ví dụ 6 – Trích xuất 5 ký tự đầu tiên của chuỗi
Đôi khi bạn có thể cần trích xuất một vài ký tự đầu tiên của chuỗi. Đây có thể là trường hợp khi bạn có dữ liệu giao dịch và năm ký tự đầu tiên [hoặc bất kỳ số lượng ký tự nào khác] đại diện cho một mã định danh duy nhất
Ví dụ: trong tập dữ liệu hiển thị bên dưới, năm ký tự đầu tiên là duy nhất cho một dòng sản phẩm
Dưới đây là các bước để nhanh chóng trích xuất năm ký tự đầu tiên từ dữ liệu này bằng Văn bản thành Cột
- Chọn tập dữ liệu
- Chuyển đến Dữ liệu → Công cụ dữ liệu → Văn bản thành cột. Thao tác này sẽ mở Trình hướng dẫn Chuyển văn bản thành Cột
- Ở Bước 1, hãy đảm bảo chọn Chiều rộng cố định . Nhấp vào Tiếp theo
- Ở Bước 2, tại phần Data preview bạn kéo đường kẻ dọc đặt sau 5 ký tự trong văn bản. Nhấp vào Tiếp theo
- Thay đổi ô đích thành ô bạn muốn có kết quả
- Nhấp vào Kết thúc
Điều này sẽ phân chia tập dữ liệu của bạn và cung cấp cho bạn năm ký tự đầu tiên của mỗi id giao dịch trong một cột và tất cả còn lại trong cột thứ hai
Ghi chú. Bạn cũng có thể đặt nhiều đường thẳng đứng để chia dữ liệu thành nhiều hơn 2 cột. Chỉ cần nhấp vào bất kỳ đâu trong khu vực Xem trước dữ liệu và kéo con trỏ để đặt dải phân cách
Ví dụ 7 – Chuyển đổi số có dấu trừ ở cuối thành số âm
Mặc dù đây không phải là điều có thể gặp phải thường xuyên, nhưng đôi khi, bạn có thể thấy mình đang sửa các số có dấu trừ ở cuối và biến các số này thành số âm
Văn bản thành Cột là cách hoàn hảo để sắp xếp này
Giả sử bạn có một tập dữ liệu như hình bên dưới
Dưới đây là các bước để chuyển đổi các dấu trừ ở cuối này thành số âm
- Chọn tập dữ liệu
- Chuyển đến Dữ liệu → Công cụ dữ liệu → Văn bản thành cột. Thao tác này sẽ mở Trình hướng dẫn Chuyển văn bản thành Cột
- Ở Bước 1, nhấp vào 'Tiếp theo'
- Ở Bước 2, nhấp vào 'Tiếp theo'
- Ở Bước 3, nhấp vào nút Nâng cao
- Trong hộp thoại Cài đặt nhập văn bản nâng cao, chọn tùy chọn 'Dấu trừ cho số âm'. Nhấp vào OK
- Đặt ô đích
- Nhấp vào Kết thúc
Điều này sẽ ngay lập tức đặt dấu trừ từ cuối số đầu của nó. Bây giờ bạn có thể dễ dàng sử dụng những con số này trong công thức và tính toán
Bạn cũng có thể thích các Hướng dẫn Excel sau
- CONCATENATE Phạm vi Excel [có và không có dấu phân cách]
- Excel AUTOFIT. Làm cho hàng/cột khớp với văn bản một cách tự động
- Cách chuyển đổi dữ liệu trong Excel
- Cách chia ô trong Excel
- Cách hợp nhất các ô trong Excel đúng cách
- Cách tìm các ô đã hợp nhất trong Excel [và loại bỏ nó]
- Cách xóa thời gian khỏi ngày trong Excel
- Cách chuyển đổi văn bản thành ngày trong Excel
SÁCH EXCEL MIỄN PHÍ
Nhận Ebook 51 mẹo Excel để tăng năng suất và hoàn thành công việc nhanh hơn
Tên
CÓ - GỬI SÁCH ĐIỆN TỬ CHO TÔI
11 suy nghĩ về “7 điều tuyệt vời mà Excel có thể chuyển văn bản thành cột cho bạn”
MANTHAN
Tháng Ba 2020 lúc 11. 46 giờ sáng
Cám ơn vì đã chia sẻ. Thích nó
d
Tháng Ba 2019 lúc 3. 22 giờ sáng
Tôi có thể bỏ qua mọi dòng khác [A2, A4, A6, A8] và chuyển chúng sang cột khác không?
Jim Hermann
Tháng Tám 2018 lúc 9. 08 giờ sáng
Làm cách nào để chuyển đổi 4 hàng của tập dữ liệu duy nhất thành một cột?
محمد حلمي
Tháng Năm 2017 lúc 10. 09 giờ tối
Tôi có câu hỏi xin vui lòng
Tôi có phạm vi giả sử từ A1 đến A100
Tôi muốn đặt con trỏ bên trong mỗi ô
Just as if I was working manually, double all in cell A1 then enter then double all in cell A2 and so on
on condition
Do not affect the contents of the cell
This action workscon sâu
Tháng mười hai 2016 tại 7. 30 giờ chiều
Điểm thứ 7 là không thể tin được, tôi đã không nhận thức được
Sumit Bansal
Tháng mười hai 2016 lúc 3. 17 giờ chiều
thật vui khi bạn thấy nó hữu ích
Torstein
Tháng mười hai 2016 lúc 3. 06 giờ chiều
Cảm ơn Sumit. Tôi thường xuyên sử dụng văn bản cho các cột, nhưng tôi không biết về khả năng "ngày không hợp lệ".
Điều đó thực sự hữu ích.
Nhưng thực tế là nó không động làm giảm giá trị khá nhiều.
Dù sao cũng cảm ơn bạn đã hướng dẫn rất hay.Sumit Bansal
Tháng mười hai 2016 lúc 3. 17 giờ chiều
Xin chào Torstein. Tôi đồng ý rằng kết quả không năng động khiến nó không phù hợp trong một số trường hợp
Jim - Omaha, NE Hoa Kỳ
Tháng mười hai 2016 lúc 10. 41 giờ chiều
Trên URL được phân tách, bạn cũng có thể kiểm tra “Coi các dấu phân cách liên tiếp là một” và loại bỏ cột trống
gim
Tháng mười hai 2016 lúc 10. 10 giơ tôi
Tôi nhận được 08. 12. Kiểu ngày tháng năm 2016 khá nhiều [đó là một thứ của SAP]
Tôi chỉ tìm kiếm và thay thế “. ” với “/” [“-” cũng phù hợp với tôi], Excel sẽ thực hiện phần còn lại
thường đi kèm với ctrl-# để có định dạng ngày hữu íchNó cũng mang lại cho tôi những khoảng thời gian như “123” cho 1. 23 giờ sáng và “12” cho 00. 12 giờ sáng
Điều này được giải quyết bằng cách sử dụng cột trợ giúp có công thức “=TIME[ref%,MOD[ref,100],]”, sau đó sao chép lại các giá trị [có thể có các giá trị thay thế vàmột vấn đề khác là các mã tham chiếu như “456E7” mà Excel vui vẻ diễn giải là 4.560.000.000 [nó cho rằng đó là định dạng khoa học] – luôn nhập các cột như TEXT