Function to move text >65 characters to next row in column?

[Nat1]

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

 

[Dave F]

Assuming your data is in A1, try something like in B1:

=IF(LEN(A1>65),LEFT(A1,65),A1)

In order to extract characters 66 and on, you need a helper column, in C:C,
which is =LEN(A1) If LEN(A1)=100, then =RIGHT(A1,35) will extract the 35
right-most characters. Put =RIGHT(A1,35) into D1.

An awkward process but it will work.

Perhaps someone has a more elegant solution.

Dave

 

[Dave F]

For extracting the 66th through nth character, use this:

=IF(LEN(A1)<=65,"",RIGHT(A1,(LEN(A1))-65))

Wow, I'm good.

Dave

 

[Nat1]

Thank-you for the reply Dave F. This formula gives me the same result as the
text to columns wizard. I'm not sure if what I'm trying to accomplish is
possible? But I need text >65 to move to the next cell within the same
column, without overwriting what's already there! I guess something like text
wrapping, but not within the same cell!

Nat1

 

[LyfordIII]

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

 

[Nat1]

That works!! Just have to figure out now how to copy the data from the
preceeding columns/cells when the text is shift down. Thank-you for the code,
it is greatly appreciated.

Nat1

 

[LyfordIII]

That works!! Just have to figure out now how to copy the data from the
preceeding columns/cells when the text is shift down. Thank-you for the code,
it is greatly appreciated.

No problem.

As to the rest of it, I was thinking of a single column of data. If
there's more, that's pretty easy. Change the macro so that instead of
selecting a cell and inserting, select that row. You'll have to make
sure that your column gets set back immediately afterward to the colum
you're checking, but that should do it, and it should be pretty
painless...