Cắt khoảng trắng, kí tự đặc biệt trong chuỗi

Thảo luận trong 'Hàm và công thức Excel' bắt đầu bởi netmatrix, 23 Tháng mười hai 2010.

  1. netmatrix

    netmatrix New Member

    Mình có 1 chuổi như thế này:
    " 00-1-099-555.98*ACD. 000"
    Bác nào giúp mình cách cắt các khoảng trắng + các kí tự đặt biệt ra khỏi chuổi trên dùm
    để được chuổi như sau: "00109955598ACD000" (Các Bác lưu ý dùm phía trước vẫn còn giữ lại được số 0)
    Mong các Bác giúp đỡ nhiệt tình
     
  2. haonlh

    haonlh Well-Known Member

    php

    Tôi viết hàm trong file đính kèm, chỉ giữ lại các số, chữ cái. bạn có thể sửa để giữ lại thêm gì đó trong dong lệnh có chữ đỏ
    PHP:
    Function loai(bc As String)
    Dim kq As String
    kq 
    ""
    For 1 To Len(bc)
    kq1 Mid(bci1)
    Select Case kq1
    [B][COLOR=red]C[/COLOR][COLOR=red]ase [/COLOR][COLOR=red]"0" To "9""a" To "z""A" To "Z"[/COLOR][/B]
    kq kq kq1
    End Select
    Next
    loai 
    kq
    End 
    Function
    Còn 1 lỗi: đó là nếu kq chỉ toàn số thì các số 0 ở đầu bị bỏ qua. Tuy nhiên sửa không khó
     

    Các file đính kèm:

    • loai.xls
      Kích thước:
      18 KB
      Đọc:
      31
    Last edited by a moderator: 23 Tháng mười hai 2010
  3. hoangdanh282vn

    hoangdanh282vn Nguyễn Cảnh Hoàng Danh

    Bạn có thể dùng công thức sau. Giả sử ô A1 chứa dữ liệu :

    PHP:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),".",""),"*","")
    Nếu có thêm ký tự đặc biệt thì bạn thêm hàm Substitute vào công thức là ok.
     
  4. netmatrix

    netmatrix New Member

    1. Cám ơn Bác haonlh và Bác hoangdanh282vn đã giúp đỡ mình tận tình.
    Đây là trường hợp mình gặp trong thực tế do phải tổng hợp, so sánh dữ liệu từ các bộ phận trong cty. Mình rất đau đầu vì trường hợp này
    Chuổi trên " 00-1-099-555.98*ACD. 000" là 1 ví dụ điển hình (tuy hơi quá) về mã hàng hoá của nhiều nhà sản xuất.
    Mình muốn cắt hết toàn bộ các khoảng trắng + kí tự đặt biệt để tinh gọn dữ liệu, chỉ cần giữ lại được chính xác số kí tự trong chuổi mà thôi (ví dụ: sao khi cắt sẽ là:"00109955598ACD000" giữ lại kể cả 2 số 0 phía trước)
    @haonlh: Mình thích cách áp dụng function của Bác Tuy nhiên mình mù tịt về VB nếu Bác có thể giúp mình hoàn chỉnh phần Code để có thể giữ lại được mấy số 0 phía trước lẫn phía sau vd: như trường hợp "004123,00" mình rất biết ơn Bác (dấu , thực ra là dấu . nhưng do người nhập liệu sai nên chắc chắn sẽ có thể xảy ra trường hợp này)
    @hoangdanh282vn: Cách sử dụng hàm sẵn có trong excell của Bác rất đơn giản và dễ sử dụng. Tuy nhiên vẫn không khắc phục được lỗi mất số 0 phía trước. Bác có cao kiến gì không?
    2. Mình vừa nghĩ ra thêm 1 giải pháp để hạn chế việc nhập liệu bị sai cũng như thống nhất cách nhập liệu giữa các bộ phận trong cty: Là định dạng sẵn 1 cột sao cho khi ta nhập dữ liệu dạng chuổi vào cột ấy vd: " 00-1-099-555.98*ACD. 000" và nhấn enter thì chuổi sẽ tự động được trả về giá trị "00109955598ACD000"
    Các Bác có cách nào giải quyết vấn đề thứ 2 mình nêu ra không?
    3. 1 lần nữa xin chân thành cám ơn 2 Bác haonlh, hoangdanh282vn đã giúp đỡ mình
    Xin hoan nghênh các thành viên khác có dịp ghé qua topic này và để lại giải pháp giúp mình giải quyết 2 vấn đề trên
     
    Last edited: 24 Tháng mười hai 2010
  5. concogia

    concogia Gội rồi mới Cạo

    Vấn đề thứ 2 của bạn không khó nhưng phải xem dữ liệu ban đầu & dữ liệu muốn lấy của bạn, nói chung nó phải có quy luật thì Ok ngay thôi
    Còn "bi" giờ tạm thời mình góp với bạn một cách làm bằng hàm tự tạo, vì có nhiều người nhập == >dữ liệu bị lung tung, nên cách mình làm là cứ những ký tự nào mà bạn cho là "đặc biệt" phải "thịt" nó thì bạn cho vào hàm nhé
    Mã:
     
    Public Function cat(Vung As Range, KtDb)
        Dim I As Integer, Tam As String
            For I = 1 To Len(Vung)
                If InStr(1, KtDb, Mid(Vung, I, 1)) Then
                    Tam = Tam
                Else
                    Tam = Tam & Mid(Vung, I, 1)
                End If
            Next
        cat = Tam
    End Function
     
    
    Thí dụ dữ liệu nằm ở A1
    Cú pháp: =cat(A1,"những ký tự muốn cắt")
     

    Các file đính kèm:

  6. haonlh

    haonlh Well-Known Member

    cái của anh concopgia hay hon cua tôi đó. Vì nó có tham số thứ 2 là các ký tự không lấy.Nếu cần giữ lại các số 0 ở đầu và ở cuối sau dấu chấm thập phân thì nên ghép thêm 1 chữ cái vô đầu. Ví dụ, dùng chữ "a" và lấy hàm của anh concopgia:= "a" & cat(A1,"những ký tự muốn cắt") Tất nhiên khi xử lý thì phải chú ý.
     
  7. ndu96081631

    ndu96081631 Well-Known Member

    Hàm thế này cũng được vậy!
    PHP:
    Function RejSymbol(Text As String)
      
    With CreateObject("VBScript.RegExp")
        .Global = 
    True: .Pattern "\W"
        
    RejSymbol = .Replace(Text"")
      
    End With
    End 
    Function
    Mấy vụ tách số, tách chữ này đâu cần đến vòng lập (code VBA có sẵn để làm việc này)
     

    Các file đính kèm:

    Last edited: 24 Tháng mười hai 2010
  8. thanhlanh

    thanhlanh New Member

    Làm cho bạn thử nè:
    Hãy gõ lung tung rồi Enter xem
     

    Các file đính kèm:

  9. netmatrix

    netmatrix New Member

    @concopgia: Thanks Bác rất nhiều, mình thích cách viết code của Bác vì có tham số cho phép lựa chọn để cắt <-- ứng dụng sẽ rộng hơn
    Vấn đề thứ 2 Bác nêu ra "Vấn đề thứ 2 của bạn không khó nhưng phải xem dữ liệu ban đầu & dữ liệu muốn lấy của bạn, nói chung nó phải có quy luật thì Ok ngay thôi" Thực ra nó có quy luật đấy chứ: Mã hàng của nhà sản xuất thường chỉ có số 0-9 kèm chữ a-Z và được ngăn cách với nhau bằng dấu ". - _ hoặc # khoảng trắng" (theo quy luật ngầm định của mỗi công ty). Các kí tự đặt biệt này chỉ đóng vai trò thứ yếu, giúp nhà sản xuất nhận dạng nhanh mã số hàng này thuộc chủng loại hoặc nhóm hàng nhất định nào đó, cho nên có hay không có thì cũng không quan trọng. Nên mình nghĩ vấn đề thứ 2 có khả năng giải quyết được.
    @ndu96081631: Bác giúp mình giải quyết bài toán với 1 hướng khác thuần VBA, code này chắc chắn chạy nhanh hơn vòng lặp (theo ý chủ quan của mình vì mình ko biết rành VBA). Bác trùm về VBA rồi, code của Bác concopgia mình còn lơ mơ hiểu đôi chút (hao hao giống C, pascal mình từng học), còn của Bác thì mình .... chịu :(. Thanks Bác đã giúp đỡ nhiệt tình. Bác có cách giúp mình giải quyết vấn đề thứ 2 được chứ ?
    @Thanhlanh: Hướng giải quyết vấn đề thứ 2 của Bác rất hay, mình gõ "lung tung" theo ý Bác thì kết quả là nó cắt mất số "0" đầu và cuối của mình rồi :( + với tác dụng trên toàn sheet không ổn bác ơi. Mình chỉ cần 1 cột thôi :D vì sheet còn chứa nhiều thứ khác nữa mà. Bác lỡ làm rồi thì làm tới luôn đi cho AE diễn đàn còn học hỏi :D . Thanks Bác nhiều
    PS: Vấn đề 1 đã giải quyết xong 1 cách tuyệt vời nhờ sự giúp đỡ nhiệt tình của các Bác. Vấn đề thứ 2 còn nan giải
    * Vấn đề thứ 2: Cần định dạng 1 cột dữ liệu sao cho khi nhập vào giá trị chuổi " 00-1-099-555.98*ACD. 000" và nhấn enter thì chuổi sẽ tự động được trả về giá trị "00109955598ACD000"
     
    Last edited: 25 Tháng mười hai 2010
  10. ndu96081631

    ndu96081631 Well-Known Member

    Hàm có rồi, giờ cứ ráp vào sự kiện Change là xong thôi
    PHP:
    Private Sub Worksheet_Change(ByVal Target As Range)
      
    Dim Clls As RangeResRng As Range
      On Error 
    GoTo ExitSub
      
    If Not Intersect(Range("A2:A1000"), TargetIs Nothing Then
        Set ResRng 
    Intersect(Range("A2:A1000"), Target)
        
    Application.EnableEvents False
        
    For Each Clls In ResRng
          
    If Clls.Value <> "" Then Clls.Value RejSymbol(Clls.Value)
        
    Next
    ExitSub
    :
        
    Application.EnableEvents True
      End 
    If
    End Sub
    Với code này thì:
    - Khi ta nhập liệu từ cell A2 đến cell A1000, code sẽ tự động chuyển đổi theo ý
    - Cho phép copy từ nơi khác paste vào nhiều cell cùng lúc
    - Cho phép thêm dòng, xóa dòng mà vẫn không gây lỗi
     

    Các file đính kèm:

    Last edited: 25 Tháng mười hai 2010
  11. thanhlanh

    thanhlanh New Member

    Sư phụ lúc nào cũng nghĩ ra những câu lệnh độc chiêu (nhưng gà như mình thì thấy khó hiểu quá)

    Gởi bạn netmatric: Mình kết hợp với code của ndu làm cho bạn file khác

    Trong sheet1:
    Nếu muốn đổi qua cột khác thì thay số 2 trên thành số khác
    Nếu muốn có tác dụng trên mọi dòng thì bỏ đoạn <And IsEmpty(ActiveCell.Value)> (nếu để nguyên thì code chỉ hoạt động khi dòng dưới còn trống)

    Trong module:
    Lưu ý: nếu muốn không bị mất số 0 đằng trước thì trước khi gõ định dạng nguyên cột là text.
     

    Các file đính kèm:

  12. ndu96081631

    ndu96081631 Well-Known Member

    Thật ra anh cũng không cần phải suy nghĩ nhiều ---> Code có sẳn tại đây http://www.giaiphapexcel.com/forum/showthread.php?16983-Tách-số-không-dùng-vòng-lặp
    cứ thế "thuộc lòng" đưa vào là được rồi (đó xem như là hằng đẳng thức đi... Ẹc... Ẹc...)
    Ngoài ra xin góp ý với anh về code cho sự kiện change:
    - Với sự kiện Change, ta không chỉ nhập liệu từng cell ---> Đôi khi copy từ nơi khác paste vào thì sao?
    - Với sự kiện Change hoạt động tại chính cell mà ta vừa gõ thì BẮT BUỘC phải có 2 dòng Application.EnableEvents = False ở đầu code và Application.EnableEvents = True ở cuối code (nếu không muốn lỗi đáng tiếc xảy ra)
    - Với sự kiện Change, có khi ta không lường hết những lỗi khác, vì thế nên dự phòng dòng On Error Goto... ở đầu nữa
     
  13. netmatrix

    netmatrix New Member

    Ẹc ... Ẹc Bác viết code đến mức ko cần suy nghĩ như học thuộc lòng quả là bái phục. thanks Bác
    Đôi khi học thuộc lòng cũng có cái hay Bác a
    Như ví dụ sau, đố các Bác câu hỏi nhỏ:
    1 cộng 1 nhân 2 = mấy ???
    Trông chờ câu trả lời trên của các Bác, mà thằng ku nhà em mới hỏi em hôm qua mà em trả lời sai, huhu
     
  14. ndu96081631

    ndu96081631 Well-Known Member

    Ấy... cái này thì vào đây mà post nhé:
    http://www.giaiphapexcel.com/forum/showthread.php?4914-Đố-vui...
    Post sai chổ bị xóa bài ráng chịu nha!
     

Chia sẻ trang này