Moving extra characters to a new column

[Tom]

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

 

[Per Jessen]

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

 

[Ron Rosenfeld]

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
==========================

 

[Tom]

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?

 

[Ron Rosenfeld]

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>)

 

[GS]

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 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.

 

[Ron Rosenfeld]

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.

 

[GS]

After serious thinking Ron Rosenfeld wrote :

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>

 

[Ron Rosenfeld]

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.

 

[Rick Rothstein]

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)

 

[Rick Rothstein]

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)

 

[GS]

Ron Rosenfeld formulated on Thursday :

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.

That's one of the key reason I like your solution better than mine!
Very nice...!

 

[Rick Rothstein]

I am just wondering if anyone can see my postings? I don't frequent these
newsgroups a lot (I do almost all of my volunteering on Microsoft's Forum),
but when I do post a message to this newsgroup, they seem to be ignored. I
am just wondering now if anyone can actually see what I post or not.

Rick Rothstein (MVP - Excel)

 

[Pete_UK]

Hi Rick,

yes, I can see it (on Google Groups) together with two other posts on
this thread. I have often felt the same myself in the past about some
of my posts being "invisible".

Pete

 

[Gord Dibben]

Rick

I see this one and the two you posted earlier to this thread.

I would say you got no reply because you did not ask a question.

You just posted good alternative code.

Did you expect a reply to either of those posts?


Happy New Year..........................Gord

 

[GS]

Rick Rothstein explained on 12/31/2010 :

I am just wondering if anyone can see my postings? I don't frequent these
newsgroups a lot (I do almost all of my volunteering on Microsoft's Forum),
but when I do post a message to this newsgroup, they seem to be ignored. I am
just wondering now if anyone can actually see what I post or not.

Rick Rothstein (MVP - Excel)

Well, I expect that the OP hasn't replied because he did not post today
(yet). Otherwise, I see many posts from you here as well as the VB
group.

Also, Happy New Year! Best of Best Wishes for 2011...

 

[Ron Rosenfeld]

I am just wondering if anyone can see my postings? I don't frequent these
newsgroups a lot (I do almost all of my volunteering on Microsoft's Forum),
but when I do post a message to this newsgroup, they seem to be ignored. I
am just wondering now if anyone can actually see what I post or not.

Rick Rothstein (MVP - Excel)

Very visible, Rick. And still showing the neat one-liners!

Happy New Year.

 

[Rick Rothstein]

To Pete, Gord, GS and Ron,

First off... Happy New Year! Second, thanks for responding guy, much
appreciated. The reason I posted my "can you see me" question is because
when Microsoft closed down their newsgroup servers, I lost access to these
newsgroup. Then I found a free service that allowed access to them, but it
only worked for one day. A month or so later, I decided to "try again" with
the same company and this time my ability to access the newsgroups stayed
active. Then I spottily posted some messages and maybe, over time, one or
two received a response of any kind. That got me to wondering if my messages
were really "out there" or just in some local servers or something that the
company I am using had access to. In the old newsgroups, it was not uncommon
for my messages to receive one, two or more replies of some kind or other,
but since Microsoft closed down their newsgroup servers and I have been
using this free access company, the majority of the messages I post receive
no replies of any kind... that just got me to wondering if the newsgroup
community at large was actually seeing them or not.

Rick Rothstein (MVP - Excel)

 

[Ron Rosenfeld]

To Pete, Gord, GS and Ron,

First off... Happy New Year! Second, thanks for responding guy, much
appreciated. The reason I posted my "can you see me" question is because
when Microsoft closed down their newsgroup servers, I lost access to these
newsgroup. Then I found a free service that allowed access to them, but it
only worked for one day. A month or so later, I decided to "try again" with
the same company and this time my ability to access the newsgroups stayed
active. Then I spottily posted some messages and maybe, over time, one or
two received a response of any kind. That got me to wondering if my messages
were really "out there" or just in some local servers or something that the
company I am using had access to. In the old newsgroups, it was not uncommon
for my messages to receive one, two or more replies of some kind or other,
but since Microsoft closed down their newsgroup servers and I have been
using this free access company, the majority of the messages I post receive
no replies of any kind... that just got me to wondering if the newsgroup
community at large was actually seeing them or not.

Rick Rothstein (MVP - Excel)

Rick,

I think the paucity of responses is probably due to the decreased traffic here since MS dropped support.