Spilt long text into different columns.

[preetipatne]

Could you please help me how to split large text into different columns without breaking word and also the 1st column width should be 30 characters,2nd column width should be 30 characters, 3rd column width should be 25 characters,4th column width should be 20.

 

[NARAYANK991]

Hi ,


Will the maximum length of text be only these 4 columns i.e. 105 characters ?


Narayan

 

[SirJB7]

Hi, preetipatne!


Try doing this:


a) Place your text in A2, 0 in C1, 30 in E1, 30 in G1, 25 in I1, 20 in K1, and 1000 (or the maximum possible remaining in M1)


b) D2: =B2+LEN(C2)


c) E2: =LEFT(MID(CONCATENATE($A2, " "),D2+1,E$1+1),IF(RIGHT(MID(CONCATENATE($A2, " "),D2+1,E$1+1),1)=" ",LEN(MID(CONCATENATE($A2, " "),D2+1,E$1+1)),LEN(MID(CONCATENATE($A2, " "),D2+1,E$1+1))-iNumber(MID(CONCATENATE($A2, " "),D2+1,E$1+1))+1))


d) Copy D2:E2 to F2:M2


e) Add this code to a VBA Module (Alt-F11, Insert, Module, and copy/paste into the right pane)


-----

Option Explicit


Function iNumber(sText As String) As Integer

iNumber = InStr(StrReverse(sText), " ")

End Function

-----


You'll get your desired data in cells E2, G2, I2, K2, M2.


Copy down D2:M2 if more than one string text (just the same number of characters as stated in row 1).


Regards!