Address can not be more than 30 characters
 

Hello,
I have a report that is opened through excel and it includes an address
column. I am sending this report to a thrid party who can not have the
address field be more than 30 characters. Is there a formula that would
recognize if the field is more than 30 characters and then move the extra
characters to the next column? the report includes over 1,000 records so it
would be nice if I did not have to do any manual work.

Thank you,

Address can not be more than 30 characters
 

It all depends on where you are allowed to strip characters from. If you just
want the first 30 then insert a column and type =Left(A1,30) copy it down and
there you go. You can copy and paste special-values over the old and delete
the column if you need to. This assumes that the address is in A1 btw.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"NeSchw6G" wrote:

Hello,
I have a report that is opened through excel and it includes an address
column. I am sending this report to a thrid party who can not have the
address field be more than 30 characters. Is there a formula that would
recognize if the field is more than 30 characters and then move the extra
characters to the next column? the report includes over 1,000 records so it
would be nice if I did not have to do any manual work.

Thank you,

Address can not be more than 30 characters
 

Sub limitcol()
mc = "J"
On Error Resume Next
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(1, mc), Cells(lr, mc))
c.Offset(, 1) = Right(c, Len(c) - 30)
c.Value = Left(c, 30)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NeSchw6G" wrote in message
...
Hello,
I have a report that is opened through excel and it includes an address
column. I am sending this report to a thrid party who can not have the
address field be more than 30 characters. Is there a formula that would
recognize if the field is more than 30 characters and then move the extra
characters to the next column? the report includes over 1,000 records so
it
would be nice if I did not have to do any manual work.

Thank you,

Address can not be more than 30 characters
 

Okay, so now that I can identify the address with 30 characters, how do I
moved the 31+ characters to a separte column?

"John Bundy" wrote:

It all depends on where you are allowed to strip characters from. If you just
want the first 30 then insert a column and type =Left(A1,30) copy it down and
there you go. You can copy and paste special-values over the old and delete
the column if you need to. This assumes that the address is in A1 btw.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"NeSchw6G" wrote:

Hello,
I have a report that is opened through excel and it includes an address
column. I am sending this report to a thrid party who can not have the
address field be more than 30 characters. Is there a formula that would
recognize if the field is more than 30 characters and then move the extra
characters to the next column? the report includes over 1,000 records so it
would be nice if I did not have to do any manual work.

Thank you,

Address can not be more than 30 characters
 

Use my code


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NeSchw6G" wrote in message
...
Okay, so now that I can identify the address with 30 characters, how do I
moved the 31+ characters to a separte column?

"John Bundy" wrote:

It all depends on where you are allowed to strip characters from. If you
just
want the first 30 then insert a column and type =Left(A1,30) copy it down
and
there you go. You can copy and paste special-values over the old and
delete
the column if you need to. This assumes that the address is in A1 btw.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"NeSchw6G" wrote:

Hello,
I have a report that is opened through excel and it includes an address
column. I am sending this report to a thrid party who can not have the
address field be more than 30 characters. Is there a formula that
would
recognize if the field is more than 30 characters and then move the
extra
characters to the next column? the report includes over 1,000 records
so it
would be nice if I did not have to do any manual work.

Thank you,

Address can not be more than 30 characters
 

Don, It looks like you have a good idea except that I have no idea what "Sub
limitcol()" means, so I don't know how to execute this. Can you simplify for
me?

Thank you!

"Don Guillett" wrote:

Sub limitcol()
mc = "J"
On Error Resume Next
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(1, mc), Cells(lr, mc))
c.Offset(, 1) = Right(c, Len(c) - 30)
c.Value = Left(c, 30)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NeSchw6G" wrote in message
...
Hello,
I have a report that is opened through excel and it includes an address
column. I am sending this report to a thrid party who can not have the
address field be more than 30 characters. Is there a formula that would
recognize if the field is more than 30 characters and then move the extra
characters to the next column? the report includes over 1,000 records so
it
would be nice if I did not have to do any manual work.

Thank you,

Address can not be more than 30 characters
 

I do not understand how to enter your code. Is it a formula?

"Don Guillett" wrote:

Use my code


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NeSchw6G" wrote in message
...
Okay, so now that I can identify the address with 30 characters, how do I
moved the 31+ characters to a separte column?

"John Bundy" wrote:

It all depends on where you are allowed to strip characters from. If you
just
want the first 30 then insert a column and type =Left(A1,30) copy it down
and
there you go. You can copy and paste special-values over the old and
delete
the column if you need to. This assumes that the address is in A1 btw.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"NeSchw6G" wrote:

Hello,
I have a report that is opened through excel and it includes an address
column. I am sending this report to a thrid party who can not have the
address field be more than 30 characters. Is there a formula that
would
recognize if the field is more than 30 characters and then move the
extra
characters to the next column? the report includes over 1,000 records
so it
would be nice if I did not have to do any manual work.

Thank you,

Address can not be more than 30 characters
 

Its a macro. Put in a module and execute from there.

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NeSchw6G" wrote in message
...
Don, It looks like you have a good idea except that I have no idea what
"Sub
limitcol()" means, so I don't know how to execute this. Can you simplify
for
me?

Thank you!

"Don Guillett" wrote:

Sub limitcol()
mc = "J"
On Error Resume Next
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(1, mc), Cells(lr, mc))
c.Offset(, 1) = Right(c, Len(c) - 30)
c.Value = Left(c, 30)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NeSchw6G" wrote in message
...
Hello,
I have a report that is opened through excel and it includes an address
column. I am sending this report to a thrid party who can not have the
address field be more than 30 characters. Is there a formula that
would
recognize if the field is more than 30 characters and then move the
extra
characters to the next column? the report includes over 1,000 records
so
it
would be nice if I did not have to do any manual work.

Thank you,