How to split text string..?

[mandarp22]

Hi All
I am trying to split the text string in to 35 characters in each line. And if the 35th character is in between a word the string should split whole word to the next column and in next column count 35 characters and again split for next column..

I am trying an array formula in first column

=IF(LEN(A5)<=35,A5,LEFT(TRIM(A5),MAX((MID(TRIM(A5),ROW(INDIRECT("1:35")),1)=" ")*ROW(INDIRECT("1:35")))))

And

=IF(LEN(A5)<=70,SUBSTITUTE(A5,C5,""),LEFT(SUBSTITUTE(TRIM(A5),C5,""),IF(MID(SUBSTITUTE(TRIM(A5),C5,""),36,1)=" ",35,MAX((MID(SUBSTITUTE(TRIM(A5),C5,""),ROW(INDIRECT("1:35")),1)=" ")*ROW(INDIRECT("1:35")))-1)))

in subsequent columns
But this formula is not working perfectly every time as shown in the sample file please help
Regards
Mandar

 

[NARAYANK991]

Hi ,

The logic is impossible to implement using a formula or even code , for that matter.

How is the software to recognize that Shanti Nagar should not be split into Shanti and Nagar , while Indira Gandhi Colony can be split into Indira Gandhi and Colony ?

Narayan

 

[mandarp22]

Hi Narayan K
it is ok even if Shanti Nagar is split as Shanti and Nagar, Logic needed is... it should split whole word till space... preferably the previous space so that the string will not go beyond the length of 35 characters...
Mandar

 

[mandarp22]

Hi Narayan K
P.S.
one more thing check the file Nr. Bus Stand, is repeated in every column. so I think some modification is needed in the formula...

 

[NARAYANK991]

Hi ,

If you use a helper column , all formulae become so simple. See the attached file.

This will work only if the length of the input address is less than 70 or 72 characters , depending on whether you use 35 or 36 as the cutoff.

Narayan

 

[bosco_yip]

Another formula option, and in non-CSE.

1] In C8, formula copy down :

=LEFT(A2,LOOKUP(40,FIND(" ",LEFT(A2,36),ROW($1:$40))))

2] In D8, formula copy down :

=SUBSTITUTE(A2,C8,)

Regards
Bosco

 

[mandarp22]

Narayan
what if the length of the input address is more then 72 characters

Bosco's formula is also good but I don't understand the logic of 40 in it and again if the length of the input address is more; then what,

please guide

Regards
Mandar

 

[NARAYANK991]

Hi ,

Specify the limit of the input string.

Narayan

 

[bosco_yip]

Bosco's formula is also good but I don't understand the logic of 40 in it and again if the length of the input address is more; then what,.....Mandar

To split the text string into max 35 characters and with a whole word at last in each line

1] Here is a "Input" testing string example of 243 characters in A1

2] Left blank in B1.

3] "Output" C1, enter formula and copy across :

=IFERROR(LEFT(MID($A1&" ",SUMPRODUCT(LEN($B1:B1))+1,300),LOOKUP(40,FIND(" ",LEFT(MID($A1&" ",SUMPRODUCT(LEN($B1:B1))+1,300),36),ROW($1:$40)))),"")

4] See attachment

Regards
Bosco

 

[Jaimezmon]

Read info here https://support.office.com/en-us/ar...unctions-49ec57f9-3d5a-44b2-82da-50dded6e4a68