Cắt khoảng trắng, kí tự đặc biệt trong chuỗi (3 người xem)

[netmatrix]

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

 

[haonlh]

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ữ đỏ

Function loai(bc As String)
Dim kq As String
kq = ""
For i = 1 To Len(bc)
kq1 = Mid(bc, i, 1)
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ó

 

[hoangdanh282vn]

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

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

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

 

[netmatrix]

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

 

[concogia]

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

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é

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")

 

[haonlh]

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ú ý.

 

[ndu96081631]

Hàm thế này cũng được vậy!

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)

 

[thanhlanh]

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?

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

 

[netmatrix]

@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 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 . 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"

 

[ndu96081631]

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"

Hàm có rồi, giờ cứ ráp vào sự kiện Change là xong thôi

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Clls As Range, ResRng As Range
  On Error GoTo ExitSub
  If Not Intersect(Range("A2:A1000"), Target) Is 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

 

[thanhlanh]

Hàm thế này cũng được vậy!

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)

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:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And IsEmpty(ActiveCell.Value) Then Application.Run ("xulychuoi")
End Sub

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:

Sub xulychuoi()
Dim chuoi As String
chuoi = RejSymbol(ActiveCell.Offset(-1, 0))
ActiveCell.Offset(-1, 0).Formula = chuoi
End Sub
Function RejSymbol(Text As String)
With CreateObject("VBScript.RegExp")
.Global = True: .Pattern = "\W"
RejSymbol = .Replace(Text, "")
End With
End Function

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.

 

[ndu96081631]

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á)

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

 

[netmatrix]

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

Ẹ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

 

[ndu96081631]

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

Ấ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!