Public Function TachChuoi(ByVal s As String, ByVal SoTu As Long, ByVal SoThuTu As Long) As String
Dim arr() As Variant, k As Long
Dim Vitri As Long
SoTu = SoTu + 1
Do While Len(s) > 0
k = k + 1
ReDim Preserve arr(1 To k)
Vitri = VBA.InStrRev(s, " ", SoTu)
If Vitri > 0 Then
arr(k) = VBA.Left(s, Vitri - 1)
s = VBA.Mid(s, Vitri + 1)
Else
arr(k) = s
s = ""
End If
Loop
If SoThuTu <= k Then TachChuoi = arr(SoThuTu)
End Function
dạ em cảm ơn anh đã giúp ạBạn sử dụng UDF:
PHP:Public Function TachChuoi(ByVal s As String, ByVal SoTu As Long, ByVal SoThuTu As Long) As String Dim arr() As Variant, k As Long Dim Vitri As Long SoTu = SoTu + 1 Do While Len(s) > 0 k = k + 1 ReDim Preserve arr(1 To k) Vitri = VBA.InStrRev(s, " ", SoTu) If Vitri > 0 Then arr(k) = VBA.Left(s, Vitri - 1) s = VBA.Mid(s, Vitri + 1) Else arr(k) = s s = "" End If Loop If SoThuTu <= k Then TachChuoi = arr(SoThuTu) End Function
Xem thêm tại đây: https://www.giaiphapexcel.com/diendan/threads/hỗ-trợ-tách-địa-chỉ-thành-nhiều-cột-theo-số-ký-tự-tương-ứng.157858/#post-1048125
Function Ws(text As String, max_length As Long) As String()
Dim b, c As Long
Dim t, teo() As String
b = 1
Do
t = Mid(text, b, max_length + 1)
If Right(t, 1) <> " " And Len(t) = max_length + 1 Then
t = Left(t, InStrRev(t, " "))
End If
b = Len(t) + b
c = c + 1
ReDim Preserve teo(c)
teo(c) = Trim(t) ': Debug.Print teo(c)
Loop While b < Len(text) And Len(t)
Ws = teo
End Function
Sub t()
Dim text As String
text = "Tr??c khi ti?n hàwwghh thi công, nhà th?u xây l?p ?ã báo cáo bi?n pháp thi công và ???c ch? ??u t?, TVGS "
Debug.Print Ws(text, 21)(3)
End Sub