On Sat, 12 Jan 2008 23:44:17 +0100, "Joergen Bondesen"
wrote:

Hi Ron.



Sorry about my bad explanation.

I have a column A, with customer information.

This information I have to print on a paper, but the width for this
information allows only 32 characters.



Therefore I must split column A info to column B, C ... ect. depending on
string length in Column A and maxlength for printing.



If "split" fragment that is greater than Max splitting length I want the
cell to be e.g. Red.



What to do with this fragment, I don't know, but I must go to the red cells
and have a look, and then take a decision or contact my customer for advice.



It is midtnight i Denmark now and I need my bed.

I'm looking forward to have a closer look at your macro in the morning.


That's a bit more clear. Try the macro below instead -- it will split the
data into adjacent columns (same row) based on the constant MaxLength (at the
top of the macro). It will also flag any words that were too long to be split
(a word is defined as being space-delimited) by making the font red; adding
asterisks before and after; and also adding a comment which will give you the
length of that word).

====================================
Option Explicit
Sub SplitSentence()
Dim c As Range
Const MaxLength As Long = 22
Dim re As Object, mc As Object, m As Object
Dim sPat As String
Dim i As Long

sPat = "\b(\S[\s\S]{1," & MaxLength - 1 _
& "}|[\s\S]{" & MaxLength + 1 & _
",}?)(\s|$)"

Set c = Selection
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
re.MultiLine = True

For Each c In Selection
'clear some area to the right
Range(c(1, 2), c(1, 10)).Clear
Set mc = re.Execute(c.Value)
i = 1
For Each m In mc
With c.Offset(0, i)
.Value = m.submatches(0)
If Len(.Value) 22 Then
.AddComment.Text "Warning: Length is " & Len(.Value) _
& " MaxLength of " & MaxLength
.Comment.Visible = False
.NumberFormat = "[Red]\*\*@\*\*"
End If
End With
i = i + 1
Next m
Next c
End Sub
====================================
--ron