Insert a special character every X characters in a text cell

[intercameleon]

Hello,

I am having difficulty with the following problem:

I have in column A 150 rows of text containing between 4 and 80 words (20 to 300 characters or so) and I need to insert /n every 80 characters without cutting words.

For example:

Cell A1
Hello my name is peter. I am married and have three kids. I work as a postman and enjoy meeting new people.

this should become something like:

Hello my name is peter. I am married and have three kids. I work /n as a postman and enjoy meeting new people.



The use of a simple text-to-column would permit me to reconstruct (using =A1&" "&B1...) the phrase and insert /n every X words but wouldn't take into consideration the number of characters (which is a problem).

If anyone knows a macro or formula that could help, I would much appreciate it.

Thanks,

Mike

 

[intercameleon]

Hello,

Ok so I have also found the solution of =substitute(A1," "," /n ",X words) but the problem again is that the number of characters is not taken into consideration...

Still haven't got a solution, can anyone help?

Thanks !

 

[MickG]

Try this:-
For data in column "A".

[COLOR="Navy"]Sub[/COLOR] MG18Feb56
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oStg
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRay()
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    num = 80
        c = 0
            Ray = Split(Dn, " ")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Ray)
    oStg = oStg & Ray(n) & " "
        ReDim Preserve nRay(c)
            nRay(c) = Ray(n)
                 c = c + 1
     [COLOR="Navy"]If[/COLOR] Len(oStg) > num [COLOR="Navy"]Then[/COLOR]
       num = num + 80
         ReDim Preserve nRay(c)
           nRay(c) = "n/"
             c = c + 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Dn = Join(nRay, " ")
 oStg = ""
  num = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[intercameleon]

Thanks a lot mick,

That's exacly what I need.

Cheers