I am reformatting spreadsheets so they can be uploaded into a
database, and need to make sure that there are no more than 255
characters in each cell. I don't want to lose the information though,
so is there VBA code I could use that would take any text over the 255
limit and move it into the cell immediatly to the the right?

Thanks

Try this:

Sub SplitData()
Dim cell As Range

For Each cell In Columns(1).Cells
If Len(cell.Value) 255 Then
cell.Offset(0, 1) = Mid(cell.Value, 256)
cell = Left(cell.Value, 255)
End If
Next
End Sub

Regards,
Per

On 30 Dec., 20:53, Tom wrote:
I am reformatting spreadsheets so they can be uploaded into a
database, and need to make sure that there are no more than 255
characters in each cell. *I don't want to lose the information though,
so is there VBA code I could use that would take any text over the 255
limit and move it into the cell immediatly to the the right?

Thanks

On Thu, 30 Dec 2010 11:53:42 -0800 (PST), Tom wrote:

I am reformatting spreadsheets so they can be uploaded into a
database, and need to make sure that there are no more than 255
characters in each cell. I don't want to lose the information though,
so is there VBA code I could use that would take any text over the 255
limit and move it into the cell immediatly to the the right?

Thanks


And if there are more than 255*2 characters?

This will just fill to the right with a maximum of 255 characters per cell.

You'll need to ensure the target range is clear.

=======================
Option Explicit
Sub Max255Chars()
Dim rg As Range, c As Range
Dim L As Long
Dim S As String
Set rg = Selection
For Each c In Selection
S = c.Text
For L = 1 To Len(S) Step 255
c(1, L \ 255 + 1).Value = Mid(S, L, 255)
Next L
Next c
End Sub
==========================

On Dec 30, 12:25*pm, Ron Rosenfeld wrote:
On Thu, 30 Dec 2010 11:53:42 -0800 (PST), Tom wrote:
I am reformatting spreadsheets so they can be uploaded into a
database, and need to make sure that there are no more than 255
characters in each cell. *I don't want to lose the information though,
so is there VBA code I could use that would take any text over the 255
limit and move it into the cell immediatly to the the right?

Thanks

And if there are more than 255*2 characters?

This will just fill to the right with a maximum of 255 characters per cell.

You'll need to ensure the target range is clear.

=======================
Option Explicit
Sub Max255Chars()
* * Dim rg As Range, c As Range
* * Dim L As Long
* * Dim S As String
Set rg = Selection
For Each c In Selection
* S = c.Text
* * For L = 1 To Len(S) Step 255
* * * * c(1, L \ 255 + 1).Value = Mid(S, L, 255)
* * Next L
Next c
End Sub
==========================

Thank you that is very helpful. Now can just run the macro in the top
cell of the column and drag it all the way down and it will still work
or does it need to be run individually?

On Thu, 30 Dec 2010 12:55:27 -0800 (PST), Tom wrote:

On Dec 30, 12:25*pm, Ron Rosenfeld wrote:
On Thu, 30 Dec 2010 11:53:42 -0800 (PST), Tom wrote:
I am reformatting spreadsheets so they can be uploaded into a
database, and need to make sure that there are no more than 255
characters in each cell. *I don't want to lose the information though,
so is there VBA code I could use that would take any text over the 255
limit and move it into the cell immediatly to the the right?

Thanks

And if there are more than 255*2 characters?

This will just fill to the right with a maximum of 255 characters per cell.

You'll need to ensure the target range is clear.

=======================
Option Explicit
Sub Max255Chars()
* * Dim rg As Range, c As Range
* * Dim L As Long
* * Dim S As String
Set rg = Selection
For Each c In Selection
* S = c.Text
* * For L = 1 To Len(S) Step 255
* * * * c(1, L \ 255 + 1).Value = Mid(S, L, 255)
* * Next L
Next c
End Sub
==========================

Thank you that is very helpful. Now can just run the macro in the top
cell of the column and drag it all the way down and it will still work
or does it need to be run individually?

It will process all the cells in "Selection"

So if your range that you want to check is in, let us say, A1:A100; just select A1:A100 and run the macro; it will put the overflow in the adjacent cell(s) (B1:B100; if needed C1:C100, etc).

Don't worry about selecting cells with fewer than 255 characters, or blank cells. It will effectively ignore those.

Be sure that your target range is clear (e.g. select B1:x100 and <delete)

Ron Rosenfeld was thinking very hard :

And if there are more than 255*2 characters?

This will just fill to the right with a maximum of 255 characters per cell.

You'll need to ensure the target range is clear.

=======================
Option Explicit
Sub Max255Chars()
Dim rg As Range, c As Range
Dim L As Long
Dim S As String
Set rg = Selection
For Each c In Selection
S = c.Text
For L = 1 To Len(S) Step 255
c(1, L \ 255 + 1).Value = Mid(S, L, 255)
Next L
Next c
End Sub
==========================

Geez Ron, that's really nice! I was working on a recursive routine when
I saw your post. I like your idea of using a loop better, but what's
with declaring/setting rg and not using it?

Here's where I was going...

Sub MaxCellChars255()
' Parses cell contents to 255[Max} characters;
' Moves excess characters to adjacent cell[s] to right.
' Recursive: Will use as many cells as required.

Dim sTemp As String, c As Range, lPos As Long

For Each c In Selection
lPos = 0
If Not IsEmpty(c) And Len(c) 255 Then
sTemp = c: c = Left$(sTemp, 255)
recheck:
sTemp = Mid$(sTemp, 256): lPos = lPos + 1
c.Offset(0, lPos) = Left$(sTemp, 255)
If Len(sTemp) 255 Then GoTo recheck
End If
Next
End Sub

While it's more self-documenting than yours, it's not as efficient.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Thu, 30 Dec 2010 16:52:21 -0500, GS wrote:

but what's
with declaring/setting rg and not using it?

Uh, user error.

I usually set rg to the range to process, and then cycle through it.

I rarely use "Selection" but, absent other information, I did in this case.

Usually I'll do something like:

set rg = range("A2", cells(cells.rows.count,"A").end(xlup))

to pick up all the active cells in column A.

After serious thinking Ron Rosenfeld wrote :
On Thu, 30 Dec 2010 16:52:21 -0500, GS wrote:

but what's
with declaring/setting rg and not using it?

Uh, user error.

I usually set rg to the range to process, and then cycle through it.

I rarely use "Selection" but, absent other information, I did in this case.

Usually I'll do something like:

set rg = range("A2", cells(cells.rows.count,"A").end(xlup))

to pick up all the active cells in column A.

Ha, ha! It sounds like you've had a long day! I suspected your
intentions but was thrown off by your use of Selection. Looked out of
place for you.<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Thu, 30 Dec 2010 23:10:40 -0500, GS wrote:

Ha, ha! It sounds like you've had a long day! I suspected your
intentions but was thrown off by your use of Selection. Looked out of
place for you.<g

Oh well. Things happen.

By the way, note that in addition to using the loop, I also made use of the Mid function characteristic that is Start is greater than string length, Mid returns a null string; and if length is greater than the number of remaining characters, it only returns to the end of the string.

So I did not have to mess around with testing to see if the cell is empty or has more than 255 characters.

No looping needed. Select the column of cells you want to process and then
run this single line of code...

Selection.TextToColumns Destination:=Selection(1), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(255, 1), Array(510, 1), Array(765,
1))

This one-liner will handle up to 1020 characters in a cell. If you need to
handle more characters, just add more Array() function call elements to the
FieldInfo's master Array() function call.

Rick Rothstein (MVP - Excel)


I am reformatting spreadsheets so they can be uploaded into
a database, and need to make sure that there are no more
than 255 characters in each cell. I don't want to lose the
information though, so is there VBA code I could use that
would take any text over the 255 limit and move it into the
cell immediatly to the the right?

I guess if you don't want to be bothered with the "do you want to replace"
question, we will need to make this a three-liner...

Application.DisplayAlerts = False
Selection.TextToColumns Destination:=Selection(1), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(255, 1), Array(510, 1), Array(765, 1))
Application.DisplayAlerts = True

Rick Rothstein (MVP - Excel)


No looping needed. Select the column of cells you want to process
and then run this single line of code...

Selection.TextToColumns Destination:=Selection(1), DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 1), Array(255, 1), Array(510, 1), Array(765,
1))

This one-liner will handle up to 1020 characters in a cell. If you need
to handle more characters, just add more Array() function call
elements to the FieldInfo's master Array() function call.