Dữ liệu chuyển từ dạng "TEXT" thành "SCIENTIFIC" và cách giữ nguyên định dạng ban đầu

levin1 · 20/8/18

Xin chào mọi người
Em có 1 file dữ liệu so sánh tồn kho của 2 bộ phận .Trong file có số lô ,mã hàng ,số lượng tồn
Trong đó có những lô hàng được đặt số lô là "01E18" ,"02E18" ... Khi thực hiện code so sánh 2 sheet dữ liệu thì số lô này bị thay đổi định dạng thành "Scientific" : từ "01E18" đổi thành "1E+18" ~ 10000000000000000 .
Không biết mọi người đã từng gặp qua trường hợp này chưa ? Nếu có thì xin hướng dẫn em cách giữ nguyên định dạng ban đầu "01E18" khi thực hiện code so sánh .
Cám ơn mọi người đã đọc bài
Chúc mọi người vui vẻ .

batman1 · 20/8/18

levin1 đã viết:
Em có 1 file dữ liệu so sánh tồn kho của 2 bộ phận .Trong file có số lô ,mã hàng ,số lượng tồn
Trong đó có những lô hàng được đặt số lô là "01E18" ,"02E18" ... Khi thực hiện code so sánh 2 sheet dữ liệu thì số lô này bị thay đổi định dạng thành "Scientific" : từ "01E18" đổi thành "1E+18" ~ 10000000000000000 .
Không biết mọi người đã từng gặp qua trường hợp này chưa ? Nếu có thì xin hướng dẫn em cách giữ nguyên định dạng ban đầu "01E18" khi thực hiện code so sánh .

Không biết tôi có hiểu đúng vấn đề không nhưng khi bạn nhập 01E18 thì nó sẽ thành ngay 1E+18 chứ đâu đợi tới lúc so sánh.
Nếu dữ liệu của bạn là chuỗi thì thay vì nhập 01E18 thì nhập '01E18 - trước là dấu nháy đơn.

levin1 · 20/8/18

batman1 đã viết:
Không biết tôi có hiểu đúng vấn đề không nhưng khi bạn nhập 01E18 thì nó sẽ thành ngay 1E+18 chứ đâu đợi tới lúc so sánh.
Nếu dữ liệu của bạn là chuỗi thì thay vì nhập 01E18 thì nhập '01E18 - trước là dấu nháy đơn.

Đúng như anh nói ,khi em nhập dữ liệu vào nó sẽ hiểu value theo dạng "SCIENTIFIC" và hiển thị ở dạng 1E+18 ngay .Em cũng không hiểu những người đã nhập dữ liệu này làm cách nào để giữ nó hiển thị ở dạng "01E18" như ở sheet "KHO" mà không dùng dấu "'" đằng trước như anh hướng dẫn .
Mong muốn của em là qua sheet "TESTSL" ,ở cột số lô phải hiển thị đúng là 01E18 thì mới đúng thực tế và có ý nghĩa so sánh .
Dữ liệu thực tế rất dài và nhiều ,nếu sửa thủ công thêm dấu "'" đằng trước thì mất nhiều thời gian ,xin phiền anh hướng dẫn 1 cách khác nhanh hơn ạ .
Em cảm ơn anh đã giúp đỡ .

congnt92 · 20/8/18

levin1 đã viết:
Đúng như anh nói ,khi em nhập dữ liệu vào nó sẽ hiểu value theo dạng "SCIENTIFIC" và hiển thị ở dạng 1E+18 ngay .Em cũng không hiểu những người đã nhập dữ liệu này làm cách nào để giữ nó hiển thị ở dạng "01E18" như ở sheet "KHO" mà không dùng dấu "'" đằng trước như anh hướng dẫn .
Mong muốn của em là qua sheet "TESTSL" ,ở cột số lô phải hiển thị đúng là 01E18 thì mới đúng thực tế và có ý nghĩa so sánh .
Dữ liệu thực tế rất dài và nhiều ,nếu sửa thủ công thêm dấu "'" đằng trước thì mất nhiều thời gian ,xin phiền anh hướng dẫn 1 cách khác nhanh hơn ạ .
Em cảm ơn anh đã giúp đỡ .

Bạn cần format toàn cột là text trước khi nhập liệu
Còn nếu đã "lỡ" nhập liệu rồi thì thử hàm này (chèn thêm cột)
C2="0"&LEFT(B2,FIND("0",TEXT(B2,"0"))-1)&"E"&LEN(B2)-FIND("0",TEXT(B2,"0"))+1
kéo xuống hết.
Copy toàn bộ kết quả có được paste value lại vào cột B và tiến hành xóa cột vừa chèn để trả lại nguyên trạng cho file.

batman1 · 20/8/18

Nếu bạn đã có hàng ngàn dữ liệu bị biến dạng rồi thì chỉ còn nước dùng code để biến chúng thành chuỗi.

Nếu bạn muốn nhờ người khác viết code thì phải mô tả dạng của So Lo.

Tôi hiểu là luôn có dạng <loạt chữ số 1>E<loạt chữ số 2>

<loạt chữ số 1> và <loạt chữ số 2> có bao nhiêu chữ số? Luôn 2 chữ số? Lúc 1, lúc 2 lúc 5? Hay <loạt chữ số 2> luôn là 18?

levin1 · 20/8/18

congnt92 đã viết:
Bạn cần format toàn cột là text trước khi nhập liệu
Còn nếu đã "lỡ" nhập liệu rồi thì thử hàm này (chèn thêm cột)
C2="0"&LEFT(B2,FIND("0",TEXT(B2,"0"))-1)&"E"&LEN(B2)-FIND("0",TEXT(B2,"0"))+1
kéo xuống hết.
Copy toàn bộ kết quả có được paste value lại vào cột B và tiến hành xóa cột vừa chèn để trả lại nguyên trạng cho file.

Cảm ơn bạn nhiều ,cách của bạn đã giúp mình giải quyết vấn đề .Chúc bạn vui vẻ

Bài đã được tự động gộp: 20/8/18

batman1 đã viết:
Nếu bạn đã có hàng ngàn dữ liệu bị biến dạng rồi thì chỉ còn nước dùng code để biến chúng thành chuỗi.

Nếu bạn muốn nhờ người khác viết code thì phải mô tả dạng của So Lo.

Tôi hiểu là luôn có dạng <loạt chữ số 1>E<loạt chữ số 2>

<loạt chữ số 1> và <loạt chữ số 2> có bao nhiêu chữ số? Luôn 2 chữ số? Lúc 1, lúc 2 lúc 5? Hay <loạt chữ số 2> luôn là 18?

Đúng là em muốn nhờ anh viết thêm code để gặp các trường hợp như em đã trình bày thì code tự hiểu và chuyển thành định dạng như mong muốn.
Cách đặt mã lô cũng như anh đã suy đoán ,khá đơn giản gồm 5 ký tự :
- 2 ký tự đầu là ký tự số chạy từ 01 tới 99 chỉ số thứ tự lô hàng
- 1 ký tự giữa là ký tự chữ cái in hoa từ A tới M chỉ số tháng : A là tháng 01 ,M là tháng 12 ...
- 2 ký tự cuối là ký tự số chỉ năm (18 là năm 2018) .
Mong anh giúp dùm để có thể tối ưu hóa file này hơn nữa .
Cảm ơn anh .

congnt92 · 20/8/18

levin1 đã viết:
Mong anh giúp dùm để có thể tối ưu hóa file này hơn nữa .

Đã hiểu ý bạn.
Nghĩa là file mà bạn đưa lên diễn đàn là đã qua việc lọc lấy các mã mà bị chuyển sang dạng SCIENTIFIC rồi.
Còn thực tế nó gồm cả những mã đại loại như 01M18, ...
Nếu vậy bạn điều chỉnh lại hàm thành
C2=IF(ISERROR(VALUE(B2)),B2,"0"&LEFT(B2,FIND("0",TEXT(B2,"0"))-1)&"E"&LEN(B2)-FIND("0",TEXT(B2,"0"))+1)
Hoặc bạn tạo 1 sheet tạm và dùng hàm trên để khi dán dữ liệu vào sheet tạm này nó xử lý. Sau đó bạn copy dán value qua sheet chính. tránh việc ảnh hưởng đến kết cấu bảng.

levin1 · 20/8/18

congnt92 đã viết:
Đã hiểu ý bạn.
Nghĩa là file mà bạn đưa lên diễn đàn là đã qua việc lọc lấy các mã mà bị chuyển sang dạng SCIENTIFIC rồi.
Còn thực tế nó gồm cả những mã đại loại như 01M18, ...
Nếu vậy bạn điều chỉnh lại hàm thành
C2=IF(ISERROR(VALUE(B2)),B2,"0"&LEFT(B2,FIND("0",TEXT(B2,"0"))-1)&"E"&LEN(B2)-FIND("0",TEXT(B2,"0"))+1)
Hoặc bạn tạo 1 sheet tạm và dùng hàm trên để khi dán dữ liệu vào sheet tạm này nó xử lý. Sau đó bạn copy dán value qua sheet chính. tránh việc ảnh hưởng đến kết cấu bảng.

Đúng như bạn nói ,file thực tế gồm nhiều các lô hàng được đặt theo quy tắc như mình trình bày và chỉ có những lô hàng sản xuất vào tháng 5 (ký tự E ở giữa) thì bị vấn đề .
Cách bạn chỉ cho mình đã giải quyết triệt để được vấn đề này rồi nhưng trong file này mình hơi quá phận 1 chút ,muốn tối giản hóa các thao tác dùng công thức mà chỉ cần sử dụng một nút "button1" trong sheet "TESTSL" do người trực tiếp sử dụng (sếp mình) không có nhiều kiến thức về excel .
Cảm ơn bạn đã giúp mình

batman1 · 20/8/18

levin1 đã viết:
Cách bạn chỉ cho mình đã giải quyết triệt để được vấn đề này rồi

Với B7 = 2.5E+19 thì công thức cho kết quả 025E18. Theo mô tả thì phải là 25E18

1 ký tự giữa là ký tự chữ cái in hoa từ A tới M chỉ số tháng : A là tháng 01 ,M là tháng 12 ...

Từ A tới M có 13 chữ cái. Nếu A là tháng 1 thì tháng 12 là L
Nếu làm bằng công thức thì có lẽ là

Mã:

=IF(ISNUMBER(B2),TEXT(MID(B2,1,FIND("0",B2)-1),"00")&"E"&LEN(B2)-FIND("0",B2)+1,B2)

levin1 · 20/8/18

batman1 đã viết:
Với B7 = 2.5E+19 thì công thức cho kết quả 025E18. Theo mô tả thì phải là 25E18

Từ A tới M có 13 chữ cái. Nếu A là tháng 1 thì tháng 12 là L
Nếu làm bằng công thức thì có lẽ là

Mã:

=IF(ISNUMBER(B2),TEXT(MID(B2,1,FIND("0",B2)-1),"00")&"E"&LEN(B2)-FIND("0",B2)+1,B2)

Cảm ơn anh đã chỉ ra sai sót ,em xin nhận sai .Như em đã trình bày ,em xin quá phận nhờ anh viết thêm code nếu có thể .
Cảm ơn anh nhiều .

batman1 · 20/8/18

levin1 đã viết:
Cảm ơn anh đã chỉ ra sai sót ,em xin nhận sai .Như em đã trình bày ,em xin quá phận nhờ anh viết thêm code nếu có thể .
Cảm ơn anh nhiều .

Tôi đề nghị code để bạn khỏi phải làm bằng tay. Nhưng sau đó thấy bạn hài lòng với công thức nên thôi. Giờ lại muốn code?

Tôi chỉ mới test với dữ liệu trong tập tin. Cũng không muốn tìm xem có tối giản được không. Viết nhanh thôi.
Dữ liệu trong sheet TESTSL, trong cột B từ dòng 2. Nếu sẽ khác thì tự sửa trong code.

Mã:

Sub toString()
Dim r As Long, k As Long, x, y As Long, a As String, cotB()
    With ThisWorkbook.Worksheets("TESTSL")
        k = .Cells(Rows.Count, "B").End(xlUp).Row
        If k = 2 Then Exit Sub
        cotB = .Range("B2:B" & k + 1).Value
    End With
    For r = 1 To UBound(cotB) - 1
        x = cotB(r, 1)
        k = InStr(1, x, "E")
        If k Then
            a = Mid(x, 1, k - 1)
            y = Mid(x, k + 1)
            k = InStr(1, a, Mid(1 / 2, 2, 1))
            If k Then
                y = y - Len(a) + k
                a = a * 10 ^ (Len(a) - k)
            End If
            cotB(r, 1) = "'" & Format(a, "00") & "E" & y
        End If
    Next r
    ThisWorkbook.Worksheets("TESTSL").Range("B2").Resize(UBound(cotB)).Value = cotB
End Sub

levin1 · 20/8/18

batman1 đã viết:
Tôi đề nghị code để bạn khỏi phải làm bằng tay. Nhưng sau đó thấy bạn hài lòng với công thức nên thôi. Giờ lại muốn code?

Tôi chỉ mới test với dữ liệu trong tập tin. Cũng không muốn tìm xem có tối giản được không. Viết nhanh thôi.
Dữ liệu trong sheet TESTSL, trong cột B từ dòng 2. Nếu sẽ khác thì tự sửa trong code.

Mã:

Sub toString() Dim r As Long, k As Long, x, y As Long, a As String, cotB() With ThisWorkbook.Worksheets("TESTSL") k = .Cells(Rows.Count, "B").End(xlUp).Row If k = 2 Then Exit Sub cotB = .Range("B2:B" & k + 1).Value End With For r = 1 To UBound(cotB) - 1 x = cotB(r, 1) k = InStr(1, x, "E") If k Then a = Mid(x, 1, k - 1) y = Mid(x, k + 1) k = InStr(1, a, Mid(1 / 2, 2, 1)) If k Then y = y - Len(a) + k a = a * 10 ^ (Len(a) - k) End If cotB(r, 1) = "'" & Format(a, "00") & "E" & y End If Next r ThisWorkbook.Worksheets("TESTSL").Range("B2").Resize(UBound(cotB)).Value = cotB End Sub

Xin cảm ơn anh trước .Em đang đăng nhập bằng điện thoại nên chưa thể sử dụng code của anh ngay được ,sẽ thử ngay khi về .Chúc anh buổi tối vui vẻ .

levin1 · 21/8/18

batman1 đã viết:
Tôi đề nghị code để bạn khỏi phải làm bằng tay. Nhưng sau đó thấy bạn hài lòng với công thức nên thôi. Giờ lại muốn code?

Tôi chỉ mới test với dữ liệu trong tập tin. Cũng không muốn tìm xem có tối giản được không. Viết nhanh thôi.
Dữ liệu trong sheet TESTSL, trong cột B từ dòng 2. Nếu sẽ khác thì tự sửa trong code.

Mã:

Sub toString() Dim r As Long, k As Long, x, y As Long, a As String, cotB() With ThisWorkbook.Worksheets("TESTSL") k = .Cells(Rows.Count, "B").End(xlUp).Row If k = 2 Then Exit Sub cotB = .Range("B2:B" & k + 1).Value End With For r = 1 To UBound(cotB) - 1 x = cotB(r, 1) k = InStr(1, x, "E") If k Then a = Mid(x, 1, k - 1) y = Mid(x, k + 1) k = InStr(1, a, Mid(1 / 2, 2, 1)) If k Then y = y - Len(a) + k a = a * 10 ^ (Len(a) - k) End If cotB(r, 1) = "'" & Format(a, "00") & "E" & y End If Next r ThisWorkbook.Worksheets("TESTSL").Range("B2").Resize(UBound(cotB)).Value = cotB End Sub

Code chạy tốt và ra kết quả đúng ý rồi .Xin cảm ơn anh một lần nữa .
Có điều ,em chưa thực sự hiểu dòng này : k = InStr(1, a, Mid(1 / 2, 2, 1))
Hàm mid(1/2,2,1) lồng bên trong có ý nghĩa gì .Em copy hàm này ra ngoài sheet thì ra kết quả là dấu "."
Kiến thức còn hạn chế nhiều ,xin nhờ anh giải thích cho ạ.

levin1 · 21/8/18

hpkhuong đã viết:
InStr(1, a, Mid(1 / 2, 2, 1))

Mid(1/2,2,1) : 1/2 (một chia 2) -> 0.5 hoặc 0,5 ->mục đích để kiểm tra dấu phân cách thập phân đang set trên máy của bạn là dấu gì đó mà...

Em đã hiểu ,cảm ơn anh

batman1 · 21/8/18

levin1 đã viết:
Code chạy tốt và ra kết quả đúng ý rồi .Xin cảm ơn anh một lần nữa .
Có điều ,em chưa thực sự hiểu dòng này : k = InStr(1, a, Mid(1 / 2, 2, 1))
Hàm mid(1/2,2,1) lồng bên trong có ý nghĩa gì .Em copy hàm này ra ngoài sheet thì ra kết quả là dấu "."
Kiến thức còn hạn chế nhiều ,xin nhờ anh giải thích cho ạ.

Thực ra tôi muốn viết mid(3/2,2,1), bạn hãy sửa lại.

Giải thích: vì mỗi người có thể có thiết lập trong CP khác nhau. Vd. bạn có dấu chấm là dấu thập phân còn tôi, đồng nghiệp hoặc đối tác của bạn, có dấu chấm phẩy là dấu thập phân.

Nếu bạn có 2.6E+19 thì khi sang máy tôi Excel chuyển thành 2,6E+19.
a ở trong code sẽ là 2.6 trên máy bạn và 2,6 trên máy tôi. Tôi muốn xem a có phần thập phân hay không thì tôi phải kiểm tra xem nó có dấu thập phân hay không. Nhưng dấu đó trên máy bạn và máy tôi khác nhau nên tôi không thể mặc định đó là dấu phẩy hay chấm phẩy. Nhưng 3/2 thì sẽ là 1.5 trên máy bạn và là 1,5 trên máy tôi. Vậy chỉ cần tìm xem a có dấu mid(3/2,2,1) hay không

Nếu cứ tìm dấu chấm thì MID là gì cho tốn điện nước. Nhưng lúc đó code chỉ chạy chuẩn trên máy bạn, còn sang máy tôi thì chạy sai bét.

Tôi dùng chiêu mid(3/2,2,1) đã từ khoảng 5 năm trước trên GPE

levin1 · 22/8/18

batman1 đã viết:
Thực ra tôi muốn viết mid(3/2,2,1), bạn hãy sửa lại.

Giải thích: vì mỗi người có thể có thiết lập trong CP khác nhau. Vd. bạn có dấu chấm là dấu thập phân còn tôi, đồng nghiệp hoặc đối tác của bạn, có dấu chấm phẩy là dấu thập phân.

Nếu bạn có 2.6E+19 thì khi sang máy tôi Excel chuyển thành 2,6E+19.
a ở trong code sẽ là 2.6 trên máy bạn và 2,6 trên máy tôi. Tôi muốn xem a có phần thập phân hay không thì tôi phải kiểm tra xem nó có dấu thập phân hay không. Nhưng dấu đó trên máy bạn và máy tôi khác nhau nên tôi không thể mặc định đó là dấu phẩy hay chấm phẩy. Nhưng 3/2 thì sẽ là 1.5 trên máy bạn và là 1,5 trên máy tôi. Vậy chỉ cần tìm xem a có dấu mid(3/2,2,1) hay không

Nếu cứ tìm dấu chấm thì MID là gì cho tốn điện nước. Nhưng lúc đó code chỉ chạy chuẩn trên máy bạn, còn sang máy tôi thì chạy sai bét.

Tôi dùng chiêu mid(3/2,2,1) đã từ khoảng 5 năm trước trên GPE

Kiến thức về excel quả là mênh mông ,chỉ 1 chi tiết nhỏ như vậy cũng cần phải tỉ mỉ suy nghĩ .
Cảm ơn anh , em đã sửa lại và sẽ lưu ý để áp dụng cho các lần sử dụng sau .

Dữ liệu chuyển từ dạng "TEXT" thành "SCIENTIFIC" và cách giữ nguyên định dạng ban đầu

levin1

Thành viên mới

File đính kèm

batman1

Thành viên gạo cội

levin1

Thành viên mới

congnt92

Thành viên hoạt động

batman1

Thành viên gạo cội

levin1

Thành viên mới

congnt92

Thành viên hoạt động

levin1

Thành viên mới

batman1

Thành viên gạo cội

levin1

Thành viên mới

batman1

Thành viên gạo cội

levin1

Thành viên mới

levin1

Thành viên mới

levin1

Thành viên mới

batman1

Thành viên gạo cội

levin1

Thành viên mới

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng