Thủ thuật tách chuỗi thành số trong excel

Ketoan.biz 17.7.17   Excel-Kế toán   Thủ thuật Excel

Ketoan.biz

Số dạng chuỗi (text) trong excel không thực hiện được các phép tính, cần chuyển chúng thành dạng số (number) để tính toán.

1. Chuyển số dạng chuỗi thành số

Excel ngầm định số đứng sau dấu ' là dạng text, chẳng hạn như '019, '020 của bảng tính sau:

Có nhiều cách để chuyển chúng thành dạng số (number) khi chúng ta có nhu cầu tính toán nào đó.

Trong trường hợp này, chúng ta sẽ tách số bằng tính năng Past Special của excel chỉ vài thao tác đơn giản:

Đầu tiên chọn vùng dữ liệu cần chuyển đổi sang dạng số, nhấn tổ hợp phím Ctr + C để copy.

Nhấn chuột phải tại nơi bạn muốn hiện kết quả, nhấn Past Special từ trình đơn tắt để hiện menu Past Special.

Trong vùng Operation chúng ta chọn Add, xong nhấn OK để xem kết quả:

Kết quả là excel đã chuyển số dạng chuỗi thành số, chỉ đơn giản vậy thôi.

2. Chuyển chuỗi ngăn cách bằng dấu phẩy thành số

Chẳng hạn chúng ta cần tách thành số từ các chuỗi sau:

Có hai tình huống để chuyển chuỗi text trên thành số: Tách thành một dảy số (bỏ dấu phẩy) hoặc tách từng số sau mỗi dấu phẩy.

+ Đối với trường hợp tách chuỗi thành một dãy số, tức chỉ bỏ dấu phẩy ('), chúng ta sử dụng tính năng Find and Replace là đơn giản nhất:

Đầu tiên bạn quét chuột lựa chọn vùng dữ liệu cần chuyển đổi sang dạng số, nhấn tổ hợp phím Ctr + F để gọi menu Find and Replace.

Chọn Replace để chuyển sang tính năng thay thế, nhập vào ô Find what dấu , (dấu phẩy). Ô Replace with chúng ta để trống.

Xong nhấn Repalce All để excel chuyển đổi chuỗi sang dảy số:

Kết quả chuyển đổi sẽ được excel thông báo như hình trên.

+ Đối với tách từng số sau các dấu phẩy

Có thể kết hợp các hàm trong excel để tách chúng thành số. Tuy nhiên với các chuỗi trên sử dụng cung cụ Text to Columns có lẻ đơn giản nhất.

Text to Columns chúng tôi đã có bài viết: Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns các bạn có thể tham khảo thêm.

3. Tách số từ chuỗi text chứa nhiều ký tự phức tạp

Dữ liệu sau đây chứa nhiều ký tự xen lẫn số khá phức tạp:

Yêu cầu chúng ta phải tách (lọc) ra những con số từ các chuỗi trên. Đây là công việc không đơn giản đòi hỏi phải có kiến thức hàm, code mới thực hiện được.

Add-ins thường được sử dụng trong trường hợp này, nhưng ở đây chúng tôi muốn sử dụng công thức kết hợp một số hàm trong excel để xử lý chúng mà không dùng vba:

Tại ô cần tách số, chúng ta gỏ vào công thức sau:

=SUM(MID(A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),ROW($A$1:INDEX($A:$A,COUNT(--MID(A2,ROW($1:$25),1))))),1)*10^(ROW(INDEX($1:$25,COUNT(--MID(A2,ROW(INDEX($1:$25,1,1): INDEX($1:$25,LEN(A2),1)),1)),1):INDEX($1:$25,1,1))-1))

Xong nhấn tổ hợp phím Ctr + Shift + Enter để tách, lọc số (đây là công thức mảng nên không được nhấn phím enter khi kết thúc lệnh). Tiếp tục copy xuống các ô bên dưới để xem kết quả.

Lứu ý: A2 là ô chứa dữ cần tách số; công thức có hiệu lực từ dòng 1 đến dòng 25 (các bạn có thể thay đổi để phù hợp với dữ liệu của mình).

Bạn thấy đó, chúng ta có thể xử lý tình huống khá phức tạp như trên bằng các hàm mặc dù công thức khá dài và khiến máy xử lý hơi chậm.

Ketoan.biz

Bài viết liên quan