More on cutting characters from one cell and pasting into another

  • Thread starter Thread starter Abay
  • Start date Start date
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
 
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...
 
oops.
hi.
a formula should do it.
=RIGHT(A1,7)
copy down. To change to hard data....edit>pastespecial>values

Regards
FSt1
 
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
 
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...
 
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.
 
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))
 
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
 
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
 
Back
Top