Hỗ trợ hàm UDF rút gọn dữ liệu bằng cách xóa bớt khoảng trắng

[Tình nghĩa giang hồ]

Chào anh chị GPE.
Do phần mềm của bên em chỉ cho phép tên có 40 ký tự thôi. Vì vậy những dữ liệu nào lớn hơn 40 ký tự thì phải rút gọn lại.
Dữ liệu của em là ở cột B, kết quả mong muốn của em là ở cột D
Quy tắc:
1_ Dữ liệu nào = hoặc nhỏ hơn 40 ký tự thì giữ nguyên. (VD như trường hợp 1 và 2 trong hình)

2_Dữ liệu nào lớn hơn 40 ký tự thì rút gọn lại bằng cách xóa khoảng trắng từ bên phải về bên trái, khi đã đủ 40 ký tự thì ngưng xóa khoảng trắng.
VD:

MFC 206MM/206MM MUFSTD 4836 FT(10-25) 1F1	41​
MFC 206MM/206MM MUFSTD 4836 FT(10-25)1F1	40​

Dữ liệu này có 41 ký tự, xóa khoảng trắng ở 1F1 sẽ còn 40 ký tự.

3_Dữ liệu nào đã rút gọn rồi nhưng vẫn lớn hơn 40 ký tự thì đánh dấu lại dùm em (như trường hợp số 8, số 10 trong hình)

HPL LKPF3250ZA1/LKPF3250ZA1 MDF MUFSTD 4817 1F1	47​
HPLLKPF3250ZA1/LKPF3250ZA1MDFMUFSTD48171F1	42​

VD như dữ liệu ở trên có 47 ký tự, xóa hết khoảng trắng vẫn còn 42 ký tự thì anh chị đánh dấu lại bằng cách trả về dữ liệu: HET CACH

Nhờ anh chị hỗ trợ giúp em trường hợp này với. Em cảm ơn anh chị.

 

[Tình nghĩa giang hồ]

Bài này em đã xử lý được rồi nhé anh chị. Em xin gửi code lại cho ai gặp vấn đề giống vậy.
Nhưng mà em vẫn thắc mắc: đối với dạng bài thế này thì công thức thông thường, không dùng VBA thì có xử lý được không anh chị ?

Function RutGonDuLieu(ByVal text As String) As String
    Dim result As String
    Dim remainingLength As Integer
    Dim i As Integer
    
    result = text
    remainingLength = Len(text) - 40
    
    If remainingLength > 0 Then
        For i = Len(text) To 1 Step -1
            If Mid(result, i, 1) = " " Then
                result = Left(result, i - 1) & Right(result, Len(result) - i)
                remainingLength = remainingLength - 1
                
                If remainingLength = 0 Then
                    Exit For
                End If
            End If
        Next i
        If Len(result) > 40 Then
            RutGonDuLieu = "HET CACH"
        Else
            RutGonDuLieu = result
        End If
    Else
        RutGonDuLieu = result
    End If
End Function

 

[tieutuvodanh192]

Với bài này chắc mấy bác trên này viết công thức dư sức bạn ạ, sáng đọc bài bạn mình nghĩ tới mấy hàm kiểu IF LEN và hàm SUBSTITUTE, sử dụng tốt tham số số 4 [instance_num] của hàm SUBSTITUTE chắc sẽ giải quyết được vấn đề. Mà đang ngồi trên xe nên chưa mò thử được

 

[LmoovoenX]

Bài này em đã xử lý được rồi nhé anh chị. Em xin gửi code lại cho ai gặp vấn đề giống vậy.
Nhưng mà em vẫn thắc mắc: đối với dạng bài thế này thì công thức thông thường, không dùng VBA thì có xử lý được không anh chị ?

Mình nghĩ là được, ý tưởng của mình như sau:
B1: dùng len(...) để loại ra những ô dưới 41 kí tự.
B2: dùng len(substitute(...," ","")) để loại ra những ô cho dù bỏ khoảng trắng vẫn dài hơn 40 ký tự.
B3: 40 - len(substitute(...," ","")) sẽ ra vị trí ký tự " " cần giữ lại. Thay thế nó bằng ký tự đặc biệt (@)
B4: cắt chuỗi tại vị trí @ thành 2 chuỗi bên phải và bên trái @. Chuỗi bên trái giữ nguyên, chuỗi bên phải thay thế hết " " bằng "". Sau đó nối chuỗi lại.

 

[bebo021999]

Cái này Ex365 làm trong vòng 1 nốt nhạc.
Mình 2016 nên thua

 

[huuthang_bd]

Cái này Ex365 làm trong vòng 1 nốt nhạc.
Mình 2016 nên thua

Sao lại thua. Excel 2007 cũng làm được.

=IF(LEN(B2)<=40;B2;IFERROR(LEFT(B2;FIND("|";SUBSTITUTE(B2;" ";"|";40-LEN(SUBSTITUTE(B2;" ";"")))))&SUBSTITUTE(MID(B2;FIND("|";SUBSTITUTE(B2;" ";"|";40-LEN(SUBSTITUTE(B2;" ";""))))+1;40);" ";"");"HET CACH"))

Bài này em đã xử lý được rồi nhé anh chị. Em xin gửi code lại cho ai gặp vấn đề giống vậy.

UDF thì nên thêm tham số chiều dài tối đa cho linh hoạt. Và có thể không cần dùng vòng lặp.

Function RutGonDuLieu(ByVal sText As String, Optional ByVal lMaxLength As Long = 40) As String
    Dim aTmp As Variant, lPos As Long
    lPos = lMaxLength - Len(Replace(sText, " ", ""))
    If lPos < 0 Then
        RutGonDuLieu = "HET CACH"
    Else
        aTmp = Split(sText, " ", lPos + 1)
        aTmp(UBound(aTmp, 1)) = Replace(aTmp(UBound(aTmp, 1)), " ", "")
        RutGonDuLieu = Join(aTmp, " ")
    End If
End Function

 

[Tình nghĩa giang hồ]

Sao lại thua. Excel 2007 cũng làm được.

=IF(LEN(B2)<=40;B2;IFERROR(LEFT(B2;FIND("|";SUBSTITUTE(B2;" ";"|";40-LEN(SUBSTITUTE(B2;" ";"")))))&SUBSTITUTE(MID(B2;FIND("|";SUBSTITUTE(B2;" ";"|";40-LEN(SUBSTITUTE(B2;" ";""))))+1;40);" ";"");"HET CACH"))

UDF thì nên thêm tham số chiều dài tối đa cho linh hoạt. Và có thể không cần dùng vòng lặp.

Function RutGonDuLieu(ByVal sText As String, Optional ByVal lMaxLength As Long = 40) As String
    Dim aTmp As Variant, lPos As Long
    lPos = lMaxLength - Len(Replace(sText, " ", ""))
    If lPos < 0 Then
        RutGonDuLieu = "HET CACH"
    Else
        aTmp = Split(sText, " ", lPos + 1)
        aTmp(UBound(aTmp, 1)) = Replace(aTmp(UBound(aTmp, 1)), " ", "")
        RutGonDuLieu = Join(aTmp, " ")
    End If
End Function

Quá hay luôn anh Thắng ơi.
Nhưng em lại có một thắc mắc khác nữa là: anh làm công việc gì: kế toán, kiểm toán hay ngân hàng........., nhiều anh chị trong diễn đàn GPE này giỏi Excel còn hơn nhiều ông thầy dạy VBA, dạy Excel ở ngoài nữa.
Không biết công việc của mấy anh là gì mà Excel lại thần sầu đến thế..........

 

[VetMini]

Quá hay luôn anh Thắng ơi.
Nhưng em lại có một thắc mắc khác nữa là: anh làm công việc gì: kế toán, kiểm toán hay ngân hàng........., nhiều anh chị trong diễn đàn GPE này giỏi Excel còn hơn nhiều ông thầy dạy VBA, dạy Excel ở ngoài nữa.
Không biết công việc của mấy anh là gì mà Excel lại thần sầu đến thế..........

"thần sầu" thì được cái gì? Cũng quanh quẩn với công thức khủng và code VBA thôi.
Sau lưng thì chê tới than lui là sếp "dốt". Trước mặt thì sếp vẫn là sếp.

 

[huuthang_bd]

anh làm công việc gì

Hồ sơ tôi có ghi đó bạn. Nghề nghiệp: Thợ đụng.

thì được cái gì?

Xin thưa bác là cũng được khá nhiều đấy ạ.

 

[VetMini]

Xin thưa bác là cũng được khá nhiều đấy ạ.

Vậy thì mình cũng ráng đua đòi, không thần sầu thì cũng thần kinh.

Function RutNgan(chuoi As String, Optional gioiHan As Long = 40) As String
If Len(chuoi) <= gioiHan Then
RutNgan = chuoi
Else
RutNgan = StrReverse(Replace(StrReverse(chuoi), " ", "", 1, Len(chuoi) - gioiHan))
If Len(RutNgan) > gioiHan Then RutNgan = "HET THUOC"
End If
End Function

 

[Tình nghĩa giang hồ]

Vậy thì mình cũng ráng đua đòi, không thần sầu thì cũng thần kinh.

Function RutNgan(chuoi As String, Optional gioiHan As Long = 40) As String
If Len(chuoi) <= gioiHan Then
RutNgan = chuoi
Else
RutNgan = StrReverse(Replace(StrReverse(chuoi), " ", "", 1, Len(chuoi) - gioiHan))
If Len(RutNgan) > gioiHan Then RutNgan = "HET THUOC"
End If
End Function

Em không nghi ngờ code của anh, nhưng em bất ngờ về code của anh.
Xưa giờ em biết anh là dân lập trình C++ hay C# gì đó.
Nhưng anh chỉ thích giúp người hỏi bài, chịu học hỏi là nhiều, còn người xin code thì hiếm khi anh viết code dùm lắm......... Phải là người nào xứng đáng thì anh mới viết code.
Lâu rồi mới thấy anh viết code lại, làm em cảm thấy vinh dự.
Cảm ơn anh.

 

[VetMini]

...
Nhưng anh chỉ thích giúp người hỏi bài, chịu học hỏi là nhiều, còn người xin code thì hiếm khi anh viết code dùm lắm......... Phải là người nào xứng đáng thì anh mới viết code.
Lâu rồi mới thấy anh viết code lại, làm em cảm thấy vinh dự.
Cảm ơn anh.

Bạn lập hồ sơ (profile) về tôi chưa đủ.
Lý do tôi viết code này là để nhắc nhở các bạn viết code rằng các bạn bị cái định kiến hàm Split nó làm mất đi sự đa dạng về giải thuật (*).
Nên nhớ rằng Basic là một trong những ngôn ngữ làm việc với dữ liệu dạng chuỗi (string) rất hiệu quả. Nó có rất nhiều hàm hổ trợ. Không cần phải nhớ hết, chỉ cần nhớ mang máng và đừng để cho sở thích (*1) của mình tạo nên thành kiến.

(*1) Ở đây, dân GPE cũng có "sở thích" về Dictionary. Thành kiến đên mức độ người hỏi bài, chả biết gì cũng "Dic" tùm lum.

Chú thích: code bài trên tuy trông ngắn gọn nhưng hiệu quả (tốc độ) còn tùy thuộc vào dạng dữ liệu.
Theo tôi tính sơ qua thì nó tối ưu khi dữ liệu thuộc về dạng 40-40-20 (40% dưới 40 ký tự, 40% rút gọn được, 20% dài quá). Và bắt đầu yếu ở 30-40-30.
Giải pháp của các bạn khác tôi chưa xem kỹ cho nên chưa ước tính được "điểm mượt".