• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

debxxx1

New Member
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"
 
Here's a function you could use.
Code:
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)

Code:
NewString = LineSplit(LString, 10)
 
Here is an alternative solution using Power Query. Here is the Mcode and a file for you to review.

Code:
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"
 

Attachments

  • Book1.xlsx
    11.7 KB · Views: 0
Back
Top