How to convert a large string into multiple lines (=10 characters)

[debxxx1]

Hi Briliant people here,

Can you please help me. I have a big string in VBA and I want to convert into a small string where each line should not contain more than 10 characters. Below is the example. Thank you in advance,

This is my Input

LString= "abc123acz1abc123acz113rfvfvejcvjcvjhvsdhvh"

This is my expected output.

SString= "abc123acz1
abc123acz1
13rfvfvejc
vjcvjhvsdh
vh"

 

[Luke M]

Here's a function you could use.

Function LineSplit(strStart As String, lngLength As Long)
    Dim i As Long
    
    'Remove any existing line breaks
    strStart = Replace(strStart, Chr(10), "")
    
    For i = lngLength To Len(strStart) Step lngLength + 1
        strStart = Left(strStart, i) & Chr(10) & Mid(strStart, i + 1)
    Next i
    
    LineSplit = strStart
End Function

and then call from either VB or a sheet itself.
=LineSplit(A2, 10)

NewString = LineSplit(LString, 10)

 

[debxxx1]

That works. Thank you so much sir.

 

[AlanSidman]

Here is an alternative solution using Power Query. Here is the Mcode and a file for you to review.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByRepeatedLengths(10), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1", type text}})
in
    #"Changed Type"

 

[Marc L]

Hi !​

convert into a small string where each line should not contain more than 10 characters.

Just with a single codeline :​

SString = Format(LString, Application.Rept("@@@@@@@@@@" & vbLf, Len(LString) \ 10))

Do you like it ? So thanks to click on bottom right Like !​

​

 

[debxxx1]

Thank you so much