Split text with length

[olamm2k]

Just to point out, though - if a line is longer than 30 characters, it will go into an unending loop.

[MartinLiss]

OK, here is a fix.

Dim intPos As Integer
    Dim strTest As String
    Dim strAddress As String
    
    strAddress = "18-G, Kuala Kangsar Road, 65890 Ipoh, Perak ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc"
    
    Do Until Len(strAddress) < 31
        strTest = Left$(strAddress, 30)
        intPos = InStrRev(strTest, ",")
        If intPos = 0 Then
            'no comma so look for a space instead
            intPos = InStrRev(strTest, " ")
        End If
        If intPos = 0 Then
            ' no space either so just artificially break it
            intPos = 30
        End If
        Debug.Print Left$(strAddress, intPos)
        strAddress = Right$(strAddress, Len(strAddress) - intPos)
        ' get rid of the leading space
        If Left$(strAddress, 1) = " " Then
            strAddress = Right$(strAddress, Len(strAddress) - 1)
        End If
    Loop
    ' Show the last part
    Debug.Print strAddress

[szlamany]

There should be no comma delimiter between a house (or lot#) and the street name.

Are the users in your locale used to entering: 123, MAIN STREET??

Is that comma needed?

[vivian2u]

yes, the user used to put delimiter between a house (or lot#) and the street name.

[MartinLiss]

Is my code anything like what you want?

[vivian2u]

Thank you. I need to try it out first. Heres are my coding, since i dont know how to fixed the length, i make it in other way. It might not in the good standard but it works. I will try yours too.

Dim strLineArr() As String
Dim strWordArr() As String
Dim intLineIdx As Integer, intWordIdx As Integer

strLineArr() = Split(txtAddress.Text, vbCrLf)

For intLineIdx = 0 To UBound(strLineArr)
strWordArr() = Split(strLineArr(intLineIdx), ",")
If Len(strWordArr(0)) < 5 Then
rs_add(1) = strWordArr(0) & "," & "" & strWordArr(1) & ","
For intWordIdx = 2 To UBound(strWordArr)
rs_add(intWordIdx) = strWordArr(intWordIdx) & ","
Next
Else
For intWordIdx = 0 To UBound(strWordArr)
rs_add(intWordIdx) = strWordArr(intWordIdx) & ","
Next
End If
Next

[szlamany]

yes, the user used to put delimiter between a house (or lot#) and the street name.

Since the comma is required between the house/lot# and street name then you cannot use SPLIT - that's obvious.

Do you always have a house/lot# in your data entry - or can there be a street name by itself?