Split text in two columns

[starchaser_one]

Hi everyone,

In the example I have in column A cells with text, which should be printed on a label. The label has a maximum of 40 characters for each line.
So if the total characters of this cell is more than 40, it should be split in 2 columns.

I've managed to split the text, but as you'll see in this example the split is mostly in the middle of a word.
This in not very readable if it is printed on a label.

Can anyone help me to find out how the split will be at the maximum of 40 char. and at the end of a word?

Many thanks

[Marc L]

According to your attachment a VBA beginner starter :

Sub Demo1() 
      Const M = 40 
        Dim V, S$(), R&, C% 
    With Blad1.Range("A2:A" & Blad1.UsedRange.Rows.Count) 
            V = .Value2 
            ReDim S(1 To UBound(V), 1) 
        For R = 1 To UBound(V) 
            If Len(V(R, 1)) > M Then 
                C = InStrRev(V(R, 1), " ", M + 1) 
                S(R, 0) = Left(V(R, 1), C - 1) 
                S(R, 1) = Mid(V(R, 1), C + 1) 
            Else 
                S(R, 0) = V(R, 1) 
            End If 
        Next 
           .Offset(, 2).Resize(, 2).Value2 = S 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[mjr veverka]

Function:

Please Login or Register  to view this content.

Where:
txt - text to split
dvdevr - text length (for the first part of the text)
prt - first or second part of 'txt' text

For example:
In 'C2' => =dvdwrds($A2; 40; 1)
In 'D2' => =dvdwrds($A2; 40; 2)
and copy down

[Marc L]

Hi porucha !

Try a little experiment : in cell A3 add a space between the dot and "B"
Then compare your result in cell C3 with mine
so you must see your pSpc allocation just needs InstrRev like I used in my demonstration for the variable C …

[jolivanes]

And what if the string is > 80 characters?

[Ghozi Alkatiri]

use this universal formula start at C2

=MID(REPT("x",40)&$A2,COLUMNS($A:A)*40+1,40)

copy cross and down

[starchaser_one]

Thanks Marc,
this works great for me.

As you already have noticed, I am a beginner I am Always surprised how the experts in this forum solve an issue in a few minutes, while I have spent hours on "google search" and still can't figure it out.

Can you explain me the "Dim line" please? I recognize dat S$() declares an array, but where stands & and % for?

[starchaser_one]

Hey Everybody,

Many thanks for all your replies. I'm going for the solution of Marc L.

Keep up the good work!

[Marc L]

Can you explain me the "Dim line" please? I recognize dat S$() declares an array, but where stands & and % for?

First thanks for the rep' !

Those characters are declaring the variables types as you can see during the execution in the VBE Locals window or
just in the VBA help for String for example. Without any character the variable is Variant but not for a constant
as VBA choose a default type according to the constant value …
It's just a legacy from the VBA grand father : the BASIC !

[starchaser_one]

Thanks for the info
I will close this post now

Have a nice day to everyone and keep it safe!