Công thức chung
=MAX(ISNUMBER(VALUE(MID(A1,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Tóm lược
Để tách một số khỏi giá trị đơn vị, bạn có thể sử dụng công thức dựa trên một số hàm: MAX, ISNUMBER, VALUE và MID. Trong ví dụ được hiển thị, công thức trong C5 là:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Lưu ý: đây là công thức thực nghiệm sử dụng hằng số mảng được mã hóa cứng, bạn đặt ở đây để tham khảo và nhận xét. Chỉ được thử nghiệm ngẫu nhiên, vì vậy hãy cẩn thận nếu bạn sử dụng hoặc thích ứng.
Giải trình
Đôi khi bạn gặp phải dữ liệu kết hợp các đơn vị trực tiếp với các số (tức là 8 km, 12v, 7,5 giờ). Thật không may, Excel sẽ coi các số ở định dạng này là văn bản và bạn sẽ không thể thực hiện các phép toán trên các giá trị như vậy.
Để tách một số khỏi giá trị đơn vị, bạn cần xác định vị trí của số cuối cùng. Nếu bạn thêm 1 vào vị trí đó, bạn có phần đầu của văn bản đơn vị. Công thức này sử dụng khái niệm này để tìm ra đơn vị đo bắt đầu từ đâu.
Trong ví dụ được hiển thị, công thức trong C5 là:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Công thức này sử dụng hàm MID để trích xuất 9 giá trị đầu tiên trong B5, mỗi lần một ký tự. Kết quả là một mảng như thế này:
("8","0","v","","","","","","")
Sau đó, chúng tôi sử dụng hàm VALUE để chuyển đổi số ở định dạng văn bản thành số thực tế. Kết quả là:
(8,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!)
Chúng tôi chạy mảng này thông qua ISNUMBER để nhận được:
(TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE)
Sau đó nhân mảng đó với một mảng khác với 9 số để được:
(1,2,0,0,0,0,0,0,0)
Sau đó, chúng tôi sử dụng MAX để lấy giá trị lớn nhất, đó là vị trí của "số cuối cùng".
Cuối cùng, chúng tôi thêm 1 vào vị trí để có được vị trí "đơn vị bắt đầu".
Cuối cùng, chúng tôi sử dụng vị trí này với các hàm LEFT và RIGHT tiêu chuẩn để tách các số khỏi các đơn vị:
=VALUE(LEFT(B5,C5-1)) // number =TRIM(RIGHT(B5,LEN(B5)-C5+1)) // unit
Lưu ý rằng hằng số mảng số được mã hóa cứng là một phương pháp hack để thuận tiện và sẽ chỉ xử lý các giá trị thô có độ dài tối đa 9 ký tự.
