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

Thủ thuật tách chuỗi thành số trong excel
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:

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

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.

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

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.

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

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

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

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:

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

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.


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

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ố:


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

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:


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




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ả.


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


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

Chia sẻ bài viết

Bài viết liên quan

Bài mới hơn
« Prev Post
Bài cũ hơn
Next Post »