Solved: Cutting out the five right letters from ActiveCell

[DanOfEarth]

Hi,

I'm trying to write a snippet that will cut the five right letters/numbers out of the Activecell (an address zip code below) so I can paste it to the next cell. Then trim the extra space. I haven't worked with VBA in eight years.

Mcallen, TX 78503

I've tried versions of Right(), Mid (), Parse(), RTrim....

I know it's got to be simple.

Thanks.

[mikerickson]

[VBA]zipCode=Right(cityStateZip, 5)[/VBA]
alternatly, if the City State Zip is in cell A1 you could put
=RIGHT(A1,5)
in B1

[DanOfEarth]

Thanks Mike,

I know that would work on a static worksheet, however I'm attaching a series of subs to a Command Button.

In my sub-procedure, I'm trying to in exact order:
1st: Activate the cell with the below info in it.

Mcallen, TX 78503

2nd: parse the information in the cell by programatically cutting the Zip out of the cell while leaving the City in the cell
3rd: then paste the Zip into a different cell.

[Paul_Hossler]

One way, not tested and could use error checking

[vba]
Sub GetZip(AddressCell As Range, ZipCell As Range)

'only use first cell if more that one passed
'pull out zip code
ZipCell.Cells(1, 1).Value = Right(AddressCell.Cells(1, 1).Value, 5)
'put in the left chars - 6 (zip 5 + 1 for space)
AddressCell.Cells(1, 1).Value = Left(AddressCell.Cells(1, 1).Value, Len((AddressCell.Cells(1, 1).Value) - 6))

End Sub
[/vba]

Paul

[DanOfEarth]

Paul,

That looks like it. If not, it's close.

However what would the syntax look like if the starting cell is the "Activecell". The reason for this is that the cell with the info in it is non-static. (the user is going to activate it first).

Would I replace:

ZipCell.Cells(1, 1).Value = Right(AddressCell.Cells(1, 1).Value, 5)

with

ZipCell.Activecell.Value = Right(AddressCell.Cells(1, 1).Value, 5)

I'm trying it in a minute but I suspect it'll be a syntax error.

[Paul_Hossler]

From what I understood, you wanted to put the zip in another cell, and leave the original (ActiveCell) "zip-less" when you ran the sub from a command button

In the command button code you could always call my earlier sub (or embed it)

[VBA]
Call GetZip(ActiveCell, ActiveCell.Offset(0,1))

or embedded

With ActiveCell
.Offset(0, 1).Value = Right(.Value, 5)
.Value = Left(.Value, Len((.Value) - 6))
End With

[/VBA]

which would put the zip one cell to the right

SOMEHOW you have to specifiy the second cell to receive the zip (if that's what you want)

Again, NOT tested and barely proof-read

Paul

[mikerickson]

If the sequence is
1)User selects a cell (containing "Davis CA 95616")
2)User presses button
3)ActiveCell gets "Davis CA", the cell to the right gets "95616"

I would use this code.
[VBA]Sub SplitOffZipFromActiveCell()
Dim zipStr As String

With ActiveCell
zipStr = Right(CStr(.Value), 5)
If zipStr Like "[0-9][0-9][0-9][0-9][0-9]" Then
.Offset(0, 1) = zipStr
.Value = Left(.Value, Len(.Value) - 5)
Else
MsgBox "Cell does not end in zipcode"
End If
End With

End Sub[/VBA]The checking for a zip code is there because users sometimes double press Command Buttons, plus they press the wrong button when wrong cells are selected.

This code only works with 5 digit zip codes, it does not detect/handle 9 digit zips.

[DanOfEarth]

OK,

I combined them both, and even added a second handler for the state as well, but it keeps flagging the "else" error handler. It was trying to parse the following into three columns:

Houston, TX 77009

Here's the code:

[VBA]
Dim zipStr As String
Dim stateStr As String
With ActiveCell
zipStr = Right(CStr(.Value), 5)
If zipStr Like "[0-9][0-9][0-9][0-9][0-9]" Then
.Offset(0, 3) = zipStr
.Value = Left(.Value, Len(.Value) - 6)
Else
MsgBox "Cell does not end in zipcode"
End If
End With
With ActiveCell
stateStr = Right(CStr(.Value), 2)
If zipStr Like "[A-Z][A-Z]" Then
.Offset(0, 2) = stateStr
.Value = Left(.Value, Len(.Value) - 3)
End If
End With
[/VBA]
Is it possibly flagging the syntax for the CStr? I've never used that before.

Edit Lucas: VBA Tags added to code

[mikerickson]

Try changing this to
[VBA]zipString = Right(Trim(CStr(.Value)), 5)[/VBA]In case there are trailin spaces in the original data.

The CStr converts to a string value and is there in case the user presses the button while there is an error value (eg #DIV/0) in the Active Cell.

[DanOfEarth]

GOT it!! You guys rock!

(I hate smileys but that was too tempting)

On a hunch, I "commented out" the if/else statements to see if there was an issue, and it hit it. Luckily the data I'm importing is in exact format, because there's no error checking on this, but it'll do for this task. Here's the code that worked.

[VBA]
With ActiveCell
zipStr = Right(CStr(.Value), 5)
.Offset(0, 3) = zipStr
.Value = Left(.Value, Len(.Value) - 6)
End With
With ActiveCell
stateStr = Right(CStr(.Value), 2)
.Offset(0, 2) = stateStr
.Value = Left(.Value, Len(.Value) - 4)
End With
[/VBA]
I've found a new home in a forum! Hopefully I'll be able to return the favor.


Edit Lucas: VBA Tags added to code

[lucas]

Hi Dan, Welcome to the forum. When you post code, select it and hit the vba button and your code will be formatted for the forum.

[DanOfEarth]

That's how ya'll did that. Thanks again. I'll be hanging out alot here.