Nat1 wrote:
Hi,
I'm looking for a way to limit the number of characters in a string of text
to 65 within a cell, so that characters than this move to the next
row(cell) in a column. This is so the data passes validation on import to an
oracle database. I have tried the text to columns wizard but that moves the
data to the next column. I have also tried =left function and validation to
no avail.

Any help would be greatly appreciated

My inclination would be to write a visual basic macro. Like this, I
think, would do what you're looking for, if I understood you
correctly...

Sub TrimTo65()

myRow = 1
Range("A" & myRow).Select
myString = ActiveCell.Value

While myString < ""

' Walk the column, as long as you don't encounter empty cells.

While Len(myString) 65

' If the string is fewer than 65 characters, no work is
required. If more, we split it up...
' and shove the next 65 characters into the current cell.

mySubString = Left(myString, 65)
ActiveCell.Value = mySubString

' ...and adjust the string

myString = Right(myString, Len(myString) - 65)

' shift everything down, to open a new cell for the remainder
of this text

myRow = myRow + 1
Range("A" & myRow).Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown

If Len(myString) < 65 Then
' OK - the remainder is fewer than 65 characters. Stick it
into the opened cell, and move on.
ActiveCell.Value = myString

End If


Wend

' advance to the next cell

myRow = myRow + 1
Range("A" & myRow).Select
myString = ActiveCell.Value

Wend

End Sub