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

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

=IF(LEN(A165),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

--
Brevity is the soul of wit.


"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

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

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

Wow, I'm good.

Dave
--
Brevity is the soul of wit.


"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

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

"Dave F" wrote:

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

=IF(LEN(A165),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

--
Brevity is the soul of wit.


"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

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

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

" wrote:

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

Nat1 wrote:
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...


Nat1

" wrote:

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

Hello again!

Thanks to everyone who has helped me out with this problem. LyfordIII, I
will be honest I'm a novice with VBA. Perhaps I should explain what my
spreadsheet consists of. I have 8 columns A:H, H is where the narrative or
text resides (the strings are of variable length). Columns A:F are primary
keys (relational database) and column G is for text sequencing (of column H),
Line 1, 2, 3 etc.

Your code works fine, but what I need is the cut text to move to the next
row/cell without creating a new cell! What I mean is that the cut text is
inserted before text in the next row/cell. If the text string in this cell is
then greater than 65 characters, the process continues. When text is shifted
down, I also need columns A:G to be copied down to the next row/cells. This
is so the primary keys match the text in column H.

I would be extremely greatful if you could help me re-write the code. I have
tried, but I'm learning at the moment!

Regards,



" wrote:


Nat1 wrote:
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...


Nat1

" wrote:

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