More on cutting characters from one cell and pasting into another

A

Abay

Hello again ... on my last request for help I asked how to cut the last 7
characters in a cell and paste them into another cell, (thanks again for
the reply) .. I need to do this for the data in a complete column in the
worksheet .. I know how to record a macro (am not knowledgeable on vba) ..
my question is how do I get it to do all rows in the worksheet?

Apologies if this is a really dumb question ... any help as usual would be
most appreciated.

Abay
 
F

FSt1

Abay said:
Hello again ... on my last request for help I asked how to cut the last 7
characters in a cell and paste them into another cell, (thanks again for
the reply) .. I need to do this for the data in a complete column in the
worksheet .. I know how to record a macro (am not knowledgeable on vba) ..
my question is how do I get it to do all rows in the worksheet?

Apologies if this is a really dumb question ... any help as usual would be
most appreciated.

Abay
Click to expand...
 
F

FSt1

oops.
hi.
a formula should do it.
=RIGHT(A1,7)
copy down. To change to hard data....edit>pastespecial>values

Regards
FSt1
 
A

Abay

Many thanks for your reply ... but I still have a problem .. Cell A1
contains a street address plus a postal code. I need to move the postal
code (last 7 chars) to a different cell, B2 and delete it from A1 for each
row in the worksheet. I understand I can use the formula and copy it down
but don't know how to delete the last 7 chars (other than manually), also
would like to change the formal to hard chars after the move. When I tried
the "paste special" the choices I got were html, Unicode text and text. I
assume I need to choose "text", but am a little lost now, I know the object
is to change the formula to hard chars, would like to do it in every row,
but think I have missed something.


Abay
 
E

Earl Kiosterud

Abay,

Non macro way. In helpers columns, put
LEFT(A2, LEN(A2)-7) =RIGHT(A2, 7)=

To make this permanent, copy the two column, then Paste-Special - Values right over
themselves. The formulas will be replaced with the current values. Now you can trash the
original column.
--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
A

AKphidelt

have you worked with Text to Columns at all?

You can hilight the cells hit Edit----> Replace
Then put in
"" to be replaced by &

Then go to Text-To-columns and Delimited---> Other and put in the & sign
Just make sure you have enough blank columns next to the column.
 
D

Dave Peterson

Maybe...

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
If Len(myCell.Value) > 7 Then
myCell.Offset(0, 1).Value = Right(myCell.Value, 7)
myCell.Value = Trim(Left(myCell.Value, Len(myCell.Value) - 7))
End If
Next myCell
End Sub


I used column A on Sheet1.

Change the sheetname if required
Change the column (twice) in this line:
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
 
A

Abay

Many many thanks to all who replied, much appreciated ... I did what you
all advised, particularly Earl & it worked like a charm ... I so much
appreciate this group, most of the time I can do what I want, learning as I
go along, but when I can't, I always get an answer here and am always
impressed by the grace and speed of replies.

Abay
 
F

FSt1

hi,
since addresses can vary in length do this
Data in a1.
in b1 enter =len(a1)-7
this will get you the number of characters in a1 minus the zip
in c1 enter = right(a1,b1)
this will get you the address minus the zip
in d1 enter = =left(a1,7)
this will get you the zip
as to the paste special problem, you may have something else on the clip
board. this sometimes occures with me when i copy something off the net to
past on the sheet.(not always) copy columns c & d...pastespecial values.
delete columns a & d.

Regards
FSt1
 
F

FSt1

hi,
your right. glad you caught that. my brain must have skidded into a ditch.

thanks
FSt1
 
Top