Số Excel được định dạng dưới dạng văn bản
Đây là một tình huống phổ biến, và may mắn thay, rất dễ xử lý. Đôi khi, các ô chỉ chứa số được gắn nhãn hoặc định dạng không chính xác dưới dạng văn bản, ngăn Microsoft Excel sử dụng chúng trong các hoạt động.
Bạn có thể thấy trong hình bên dưới, các ô trong cột A được định dạng dưới dạng văn bản. Bạn cũng có thể thấy một lá cờ màu xanh lá cây ở góc trên cùng bên trái của mỗi ô.
Chuyển văn bản thành số trong Excel
Nếu bạn thấy cờ màu xanh lá cây ở góc trên cùng bên trái, hãy chọn một hoặc nhiều ô, nhấp vào dấu hiệu cảnh báo và chọn Convert to Number.
Hoặc, chọn các ô và, trong menu Number Format trên Ribbon, chọn tùy chọn Number mặc định.
Nếu bạn cần thêm tùy chọn chi tiết, nhấp chuột phải vào (các) ô được highlight và chọn Format Cells, menu tương ứng sẽ mở. Tại đây, bạn có thể tùy chỉnh định dạng số và thêm hoặc xóa số thập phân, v.v...
Áp dụng định dạng số với tính năng Paste Special của Excel
Để phương thức này hoạt động, bạn sẽ cần nhập một số (bất kỳ số nào) vào một ô. Điều quan trọng là ô này cũng được định dạng là số. Sao chép ô đó. Bây giờ, chọn tất cả các ô mà bạn muốn chuyển đổi sang định dạng số, đi đến Home > Paste > Paste Special, chọn Formats để chỉ dán định dạng của ô đã sao chép ban đầu, sau đó bấm OK.
Trích xuất số hoặc văn bản từ các ô định dạng hỗn hợp
Bây giờ, ta sẽ đến phần khó hơn: Lấy số ra khỏi các ô có chứa nhiều định dạng đầu vào. Nếu có một số kèm theo đơn vị, bạn sẽ gặp phải vấn đề này. Để giải quyết nó, hãy xem xét một vài cách khác nhau để phân chia các ô thành số và văn bản.
Tách riêng số từ văn bản
Nếu bạn có nhiều ô chứa hỗn hợp số và văn bản, việc tách chúng theo cách thủ công có thể mất nhiều thời gian. Tuy nhiên, bạn có thể sử dụng chức năng Text to Columns của Microsoft Excel.
Chọn các ô mà bạn muốn chuyển đổi, đi đến Data > Text to Columns và sử dụng trình hướng dẫn để đảm bảo các ô xuất hiện chính xác. Đối với hầu hết các phần, bạn sẽ chỉ cần nhấp vào Next và Finish, nhưng hãy chắc chắn rằng bạn chọn một dấu phân cách phù hợp; trong ví dụ này là một dấu phẩy.
Nếu chỉ có các số có 1 và 2 chữ số, tùy chọn Fixed Width cũng có thể hữu ích, vì nó sẽ chỉ tách ra hai hoặc ba ký tự đầu tiên của ô. Bạn thậm chí có thể tạo ra một số chia tách theo cách đó.
Lưu ý: Các ô được định dạng dưới dạng văn bản sẽ không tự động xuất hiện với định dạng số (hoặc ngược lại), có nghĩa là bạn vẫn phải chuyển đổi các ô này như được mô tả ở trên.
Trích xuất một số hoặc văn bản từ một chuỗi phân cách
Phương pháp này hơi phức tạp, nhưng hoạt động rất tốt trên các bộ dữ liệu nhỏ. Hàm chính sẽ sử dụng ở đây là LEFT, trả về các ký tự ngoài cùng bên trái từ một ô. Tham khảo bài viết: Hàm LEFT, cách dùng hàm cắt chuỗi ký tự bên trái trong Excel để biết cách thực hiện.
Nếu muốn tách phần văn bản, bạn có thể sử dụng hàm RIGHT theo cùng một cách. Tham khảo bài viết: Hàm RIGHT, cách dùng hàm cắt chuỗi ký tự bên phải trong Excel để biết thêm chi tiết.
Trích xuất số từ một đầu của chuỗi liên tục
Sẽ ra sao nếu không có dấu phân cách giữa số và văn bản?
Nếu trích xuất số từ bên trái hoặc bên phải của chuỗi, bạn có thể sử dụng một biến thể của công thức LEFT hoặc RIGHT được thảo luận ở trên:
=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))
=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))
Lệnh này sẽ trả về tất cả các số từ bên trái hoặc bên phải của chuỗi.
Nếu trích xuất số từ bên phải của chuỗi, bạn cũng có thể sử dụng quy trình hai bước. Đầu tiên, xác định vị trí của chữ số đầu tiên trong chuỗi bằng hàm MIN. Sau đó, bạn có thể sử dụng thông tin đó làm biến cho công thức RIGHT, để phân tách số khỏi văn bản.
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
=RIGHT(A1, LEN(A1)-B1+1)
Lưu ý: Khi sử dụng các công thức này, hãy nhớ rằng bạn có thể phải điều chỉnh các ký tự cột và số ô.
Trích xuất số từ cả hai đầu của một chuỗi liên tục
Với các cách trên, bạn sẽ có thể trích xuất số hoặc văn bản trong hầu hết những ô có định dạng hỗn hợp. Ngoài ra, bạn có thể kết hợp chúng với một số hàm văn bản mạnh mẽ có trong Microsoft Excel để có được các ký tự đang tìm kiếm. Tuy nhiên, có một số tình huống phức tạp đòi hỏi những giải pháp phức tạp hơn.