Chuyển đổi dữ liệu text sang số trong trường hợp này?

[Người Đưa Tin]

Con gái cưng gởi file qua nhờ mình giúp bài toán này. Nhờ các bạn giúp mình chuyển đổi dữ liệu text sang số cho cột H và I. Chú ý: File nguyên thủy là pdf đã converter sang xls.
Xin cám ơn.

 

[ngocmaipretty]

J3 = IF(H3="",0,--SUBSTITUTE(SUBSTITUTE(H3,".","");CHAR(10),""))

Fill qua phải và xuống dưới. Huynh Gân hôm nay sao lại hỏi thay con gái, sao không bảo con gái tự hỏi cho quen. Mai mốt lấy chồng thì làm sao? Chả lẽ: Bố ơi, bố hỏi mẹ chồng con dùm con xem hũ muối mẹ con để ở đâu?

Í, ủa? Sao huynh Gân convert thế nào mà ba hồi dấu chấm hàng ngàn, ba hồi dấu phẩy hàng ngàn vậy kà?
Vậy là công thức trên hông có xài được rồi.

 

[Người Đưa Tin]

J3 = IF(H3="",0,--SUBSTITUTE(SUBSTITUTE(H3,".","");CHAR(10),""))

Fill qua phải và xuống dưới. Huynh Gân hôm nay sao lại hỏi thay con gái, sao không bảo con gái tự hỏi cho quen. Mai mốt lấy chồng thì làm sao? Chả lẽ: Bố ơi, bố hỏi mẹ chồng con dùm con xem hũ muối mẹ con để ở đâu?

Công thức trên vẫn sử dụng trong trường hợp này được sư nương ạ. Có điều em không rõ khi chuyển file từ pdf sang excel lại có tình trạng dữ liệu đọc vẫn thấy là đến hàng trăm nghìn nhưng áp dụng công thức trên sao nó cắt đi 000 (3 số không). Em không rõ chuyển đổi dữ liệu từ pdf sang excel như thế nào.

Í, ủa? Sao huynh Gân convert thế nào mà ba hồi dấu chấm hàng ngàn, ba hồi dấu phẩy hàng ngàn vậy kà?
Vậy là công thức trên hông có xài được rồi.

Em converter tại đây đó sư nương ạ. http://pdftoexcelonline.com/ (Sao em xem file converter đều là dấu chấm cả mà sư nương). Xin cám ơn sư nương đã nhanh chóng hỗ trợ.

 

[huuthang_bd]

Con gái cưng gởi file qua nhờ mình giúp bài toán này. Nhờ các bạn giúp mình chuyển đổi dữ liệu text sang số cho cột H và I. Chú ý: File nguyên thủy là pdf đã converter sang xls.
Xin cám ơn.

Cái này chắc phải dùng Macro. Bác dùng cái này thử xem sao:

Sub ToNumber()
Dim Cll As Range
Application.ThousandsSeparator = ","
Application.DecimalSeparator = "."
Application.UseSystemSeparators = False
For Each Cll In Selection
    If IsNumeric(Replace(Cll.Text, ".", "")) Then Cll.Value = CLng(Replace(Cll.Text, ".", ""))
Next
Application.UseSystemSeparators = True
Selection.NumberFormat = "General"
Selection.HorizontalAlignment = xlGeneral
End Sub

Copy code vào Module, quay lại Excel chọn vùng dữ liệu cần converter và chạy Macro.
Lưu ý: Code này có thể sai trong trường hợp dữ liệu có phần thập phân.

 

[ngocmaipretty]

Eureka! hahaha! đã tìm ra!

Huynh Gân chụp hình thì tất cả là dấu chấm, vì máy huynh set control panel dấu chấm thập phân. Thực tế, số nào 1 dấu chấm (nhỏ hơn 1 triệu trên thực tế), sẽ bị coi là dấu chấm thập phân, và convert thành số và 3 số cuối bị coi là số thập phân. Số nào 1 triệu trở lên, tức là có 2 dấu chấm trở lên, bị coi là text.

Bỏ định dạng canh trái sẽ thấy: Text nằm bên trái, Số nằm bên phải.



Còn muội set control panel là dấu phẩy thập phân, nên thấy text thì có 2 hoặc 3 dấu chấm, số thì 1 dấu phẩy và 3 số không thập phân.



Nếu chưa bỏ canh trái thì nhìn giống như dấu phẩy hàng ngàn.



Biết bịnh rồi thì trị thôi haha:

J3 =IF(H3="",0,IF(ISNUMBER(H3),H3*1000,--SUBSTITUTE(SUBSTITUTE(H3,".",""),CHAR(10),"")))

 

[huuthang_bd]

Eureka! hahaha! đã tìm ra!

Huynh Gân chụp hình thì tất cả là dấu chấm, vì máy huynh set control panel dấu chấm thập phân. Thực tế, số nào 1 dấu chấm (nhỏ hơn 1 triệu trên thực tế), sẽ bị coi là dấu chấm thập phân, và convert thành số và 3 số cuối bị coi là số thập phân. Số nào 1 triệu trở lên, tức là có 2 dấu chấm trở lên, bị coi là text.

Bỏ định dạng canh trái sẽ thấy: Text nằm bên trái, Số nằm bên phải.

View attachment 58989

Còn muội set control panel là dấu phẩy thập phân, nên thấy text thì có 2 hoặc 3 dấu chấm, số thì 1 dấu phẩy và 3 số không thập phân.

View attachment 58990

Nếu chưa bỏ canh trái thì nhìn giống như dấu phẩy hàng ngàn.

View attachment 58992

Biết bịnh rồi thì trị thôi haha:

J3 =IF(H3="",0,IF(ISNUMBER(H3),H3*1000,--SUBSTITUTE(SUBSTITUTE(H3,".",""),CHAR(10),"")))

Cách này không được đâu bạn ơi. Trong trường hợp dữ liệu ban đầu là một số nhỏ hơn 1000 thì công thức của bạn cũng nhân với 1000. Kết quả sẽ nhân lên 1000 lần.
Ví dụ 500 sẽ trả về 500.000

 

[sealand]

Bác Trí ơi, bác để ý cái trình convert của bác nó chèn vào cuối kết quả của ô 1 ký tự đặc biệt. Bác cứ loại trừ nó đi là xong.
Em tham gia công thức sau bác Test lại nha, em thử sửa 1 o thành có lẻ thập phân cũng được

=IF(LEN(SUBSTITUTE(CLEAN(SUBSTITUTE(H3,".","")),",","."))>0,SUBSTITUTE(CLEAN(SUBSTITUTE(H3,".","")),",",".")*1,0)

 

[ngocmaipretty]

Cách này không được đâu bạn ơi. Trong trường hợp dữ liệu ban đầu là một số nhỏ hơn 1000 thì công thức của bạn cũng nhân với 1000. Kết quả sẽ nhân lên 1000 lần.
Ví dụ 500 sẽ trả về 500.000

Cám ơn bạn Hữu Thắng nhen, nhưng ngocmai đã xem trong 3 sheet không có số nào nhỏ hơn 1000, vả lại, đã lâu lắm rồi kế toán không phải hạch toán số tiền dưới 1000 đồng vn nữa rồi. Giá chót là 10000, VAT 1000
(Hic, xăng mới lên giá 19.300 lúc 10 giờ sáng nay, chắc từ mai khỏi hạch toán cái gì dưới 10.000 luôn quá)

=IF(LEN(SUBSTITUTE(CLEAN(SUBSTITUTE(H3,".","")),", ","."))>0,SUBSTITUTE(CLEAN(SUBSTITUTE(H3,".","")), ",",".")*1,0)

Công thức của bác "xy lanh" không dùng được với số chẵn ngàn dưới 1 triệu rồi. Thí dụ số mà bác thấy tại ô H4 và I5 là 55.000, thực chất nó là 55 và định dạng 3 số thập phân, trên thanh công thức cũng hiển thị 55. Kết quả công thức "Xy lanh" là 55, đúng ra phải là 55000

Tương tự, là ô H10, H16, ...

Nó chỉ đúng cho số không chẵn ngàn thôi.

 

[ptm0412]

Lão Gân ơi, lão đừng dùng mấy trang web Tây miễn phí đó convert ra Excel. Chẳng thà Bác convert pdf ra word, rồi copy word qua excel, còn dễ xử hơn. Lý do không phải phần mềm của người ta lỗi, mà là do pdf của lão định dạng số với dấu chấm hàng ngàn, Tây nó không hiểu: 1 chấm thì là chấm thập phân, 2 chấm trở lên thì là text.

Vậy, chẳng thà nhờ nó chuyển toàn bộ ra text, còn dễ xử lý hơn, chứ nó chuyển khi thì text khi thì số thì chết.

 

[sealand]

Công thức của bác "xy lanh" không dùng được với số chẵn ngàn dưới 1 triệu rồi. Thí dụ số mà bác thấy tại ô H4 và I5 là 55.000, thực chất nó là 55 và định dạng 3 số thập phân, trên thanh công thức cũng hiển thị 55. Kết quả công thức "Xy lanh" là 55, đúng ra phải là 55000

Tương tự, là ô H10, H16, ...

Nó chỉ đúng cho số không chẵn ngàn thôi.

Chị ngocmaipretty chưa kiểm tra kỹ dữ liệu ở đoạn này rồi. Đây là vấn đề huuthang_bd đã phát hiện và nêu ra, nó thực chất chỉ là định dang thôi chứ không phải dữ liệu như vậy. Đây là các ô đã được thừa nhận đủ điều kiện chuyển sang dạng Number, ta có thể kiểm tra bằng hàm Value(). Các ô này lại được Format theo dạng (#,##0.000_);-#,##0.000 và nó làm ta sai lầm nếu chỉ cảm nhận bằng thị giác.
Cũng giống tình trạng nếu ô A1 ghi tiền giá trị 500 định dạng 0 "VND", nó sẽ hiển thị 500 VND. Nếu chỉ lấy số mà dùng công thức =left(A1,Len(A1)-3) thì hỏng mất

 

[ngocmaipretty]

Chị ngocmaipretty chưa kiểm tra kỹ dữ liệu ở đoạn này rồi. Đây là vấn đề huuthang_bd đã phát hiện và nêu ra, nó thực chất chỉ là định dang thôi chứ không phải dữ liệu như vậy. Đây là các ô đã được thừa nhận đủ điều kiện chuyển sang dạng Number, ta có thể kiểm tra bằng hàm Value(). Các ô này lại được Format theo dạng (#,##0.000_);-#,##0.000 và nó làm ta sai lầm nếu chỉ cảm nhận bằng thị giác.
Cũng giống tình trạng nếu ô A1 ghi tiền giá trị 500 định dạng 0 "VND", nó sẽ hiển thị 500 VND. Nếu chỉ lấy số mà dùng công thức =left(A1,Len(A1)-3) thì hỏng mất

Bác "xy lanh" ơi ời! Bác chưa thấy vấn đề "gốc rễ" rồi. Chính là ở chỗ mà huynh chết tiệt vừa nêu: trong file pdf gốc, số định dạng dấu chấm hàng ngàn, (dấu phẩy thập phân). Khi anh Tây kia chuyển thành Excel, số nào có 2 dấu chấm trở lên, anh í cho là text, và cho thêm ký tự xuống dòng vào. Số nào có 1 dấu chấm hành ngàn, anh í cho rằng dấu chấm đó là dấu chấm thập phân, và anh í chuyển thành số với 3 ký số thập phân.
Vậy:
- pdf 55.000 (năm mươi lăm ngàn), chuyển sang xls thành 55.000 (năm mươi lăm và không phần ngàn)
- pdf 500.000 (năm trăm ngàn), chuyển sang xls thành 500.000 (năm trăm và không phần ngàn)
- pdf 127.273 (một trăm hai mươi bảy ngàn hai trăm bảy mươi ba), chuyển sang xls thành 127.273 (một trăm hai mươi bảy và hai trăm bảy mươi ba phần ngàn).

Theo kết quả tính của Bác:

Chả lẽ "Phí dịch vụ EMS" ở ô H4 có 55 đồng thôi? Xăng 19.300 đồng 1 lít rồi bác ơi.
Chả lẽ "mua máy móc" ở ô H9 5 triệu đồng và VAT 10% ở ô H10 là 500 đồng? (Kiểm chứng: I11 = 5 triệu rưỡi)
Chả lẽ :mua đồ dùng VP" 1 triệu 950 ngàn, VAT 10% chỉ có 195 đồng? (Kiểm chứng đối ứng 111 = 2 triệu, 145 ngàn)

Ngoài ra, dòng cuối là dòng tổng mang sang trang sau, bác đã thử tính tổng của bác so với tổng của dữ liệu gốc chưa?
Về vụ này Bác yên tâm, ngocmai dùng công thức nhân 1000 là đã kiểm tra số tổng rồi.

 

[huuthang_bd]

Cám ơn bạn Hữu Thắng nhen, nhưng ngocmai đã xem trong 3 sheet không có số nào nhỏ hơn 1000, vả lại, đã lâu lắm rồi kế toán không phải hạch toán số tiền dưới 1000 đồng vn nữa rồi. Giá chót là 10000, VAT 1000
(Hic, xăng mới lên giá 19.300 lúc 10 giờ sáng nay, chắc từ mai khỏi hạch toán cái gì dưới 10.000 luôn quá)



Công thức của bác "xy lanh" không dùng được với số chẵn ngàn dưới 1 triệu rồi. Thí dụ số mà bác thấy tại ô H4 và I5 là 55.000, thực chất nó là 55 và định dạng 3 số thập phân, trên thanh công thức cũng hiển thị 55. Kết quả công thức "Xy lanh" là 55, đúng ra phải là 55000

Tương tự, là ô H10, H16, ...

Nó chỉ đúng cho số không chẵn ngàn thôi.

Đành rằng là trong dữ liệu của Bác Trí không có số nào dưới 1.000 nhưng bạn nói "đã lâu lắm rồi kế toán không phải hạch toán số tiền dưới 1000 đồng vn nữa rồi" là không đúng. Dẫu vật giá có leo thang thì trong kế toán vẫn có những chênh lệch khi thanh toán, chênh lệch tỷ giá hối đoái,... Đôi khi chỉ lệch 1 đồng thôi cũng phải mất cả ngày trời để kiểm tra lại đó.

 

[ngocmaipretty]

Đành rằng là trong dữ liệu của Bác Trí không có số nào dưới 1.000 nhưng bạn nói "đã lâu lắm rồi kế toán không phải hạch toán số tiền dưới 1000 đồng vn nữa rồi" là không đúng. Dẫu vật giá có leo thang thì trong kế toán vẫn có những chênh lệch khi thanh toán, chênh lệch tỷ giá hối đoái,... Đôi khi chỉ lệch 1 đồng thôi cũng phải mất cả ngày trời để kiểm tra lại đó.

Ngocmai cũng chỉ nói vui thôi mà. Tại thấy xăng lên giá nên bức xúc quá thôi. Một đồng cũng phải tìm cho ra thì kế toán ai cũng gặp phải, chênh lệch tỷ giá vài chục, vài trăm đồng cũng có luôn.
Mình không biết VBA, nhưng thấy Huuthang làm VBA chạy tốt rồi, thêm 1 công thức cho huynh Gân chọn lựa ấy mà. Vả lại, mình không chỉ kiểm tra dữ liệu 3 sheet của huynh Gân, mà còn kiểm tra số tổng từng trang, và tổng mang sang nữa. Cho huynh ấy xài cho 1 file này thôi.