What's wrong with my Excel macro?

Factual Questions
1

Data from city hall is provided to us about new businesses in town. We are given 9 digit ZIPs. I want to chop that down to the 5 digit version for making sample business cards.

I turned on “Use Relative References”, clicked “record macro”, double clicked on the address cell at the end, hit Delete 5 times, and Enter.

Running this macro now makes the address of every business identical to the business I used as the example. Here is the code I made, apparently. How can I fix it? Sorry, I don’t know any VB.
Sub fivezip()
’
’ fivezip Macro
’
’ Keyboard Shortcut: Ctrl+q
’
ActiveCell.Select
ActiveCell.FormulaR1C1 = “SANTA BARBARA, CA 93103”
ActiveCell.Offset(1, 0).Range(“A1”).Select
End Sub

2

I don’t know what’s wrong with your macro, but here’s an easy way to get 5-digit zip codes from 9-digit zip codes:

The function is the LEFT function. So, enter this in a cell somwhere to the right (actually, really doesn’t matter – a blank column anywhere):

=LEFT(A1;5) (where A1 is the first cell containing a 9-digit zip). That tells Excel to return the first five characters from the left in cell A1.

Then copy down as needed.

You can, if you want, copy the column you’ve created, and paste values only into the column where the 9-digit zips are.

3

Wait, it looks like you’ve got the whole address in one cell. The LEFT function won’t help you, in that case.

Let me think about this a bit. . .

4

Here’s the function:

=LEFT(A1; LEN(A1)-5)

This returns the contents of cell A1, minus the last five characters (the dash and the four digits).

This way, it doesn’t matter how many characters you’ve got before the zip code.

5

Recording doesn’t work for manipulation of values in cells. As you saw, it simply records the actual value, not the steps you are taking.

Saintly Loser’s suggestion of a formula is the easiest way to deal with this. But if you really had your heart set on a macro, the code below strips off the last 5 digits.


Sub FiveZip()
    If Len(ActiveCell.Value) > 5 Then
        ActiveCell.Value = Left(ActiveCell.Value, Len(ActiveCell.Value) - 5)
    End If
End Sub
6

I retyped the text in that picture into the Excel macro debugger, but I get a syntax error. I had to use shift-enter to get the lines to break. My indenting isn’t the same. I don’t know if that’s important. I’m sorry, I’m really ignorant of this.

7

EDIT TO PREVIOUS:

I copied that macro script into the Excel macro debugger, and it works. Thanks a lot. What could I put in there so it goes to the next cell below every time, so I can just hold down the ctrl-command and let it run?

Also, if Saintly Loser’s solution is more elegant, where is this code used? I just have no idea how I would use that. Again, it would also help if it advanced down the column on its own, too.

8

It’s not code, it’s just a function. You could copy it into a blank column somwhere over on the right of your spreadsheet. Then just copy that cell downwards as far as needed (so if you had 100 rows with addresses and zip codes, you’d copy it down 100 times). It will show the address with only a 5-digit zip code.

9

You’re comfortable with coding, but not formulas? Part of my job is VB.NET and I still avoid VBA if there is a function to do it. Use the function insert (location depends on Excel version) to give full descriptions of how to use whatever you need.

Just type an equals sign into any cell ( = ) and then the code. If you want the 5-digit ZIP, I’d use this to get just the ZIP:

=LEFT(RIGHT(A1,9),5)

Where A1 is the first entry, drag down to cover others. This is providing the last 9 digits are always a zip code, and no country, etc. This gives you just the zip. Saintly Loser’s will give the street address and ZIP5, although the semicolon should probably be a comma in Excel (semicolon in OpenOffice and similar).

10

No, a semi-colon, at least in Excel 2010. It works for me – just checked.

11

The word “function” was not a clue to me that that this was to be entered into an Excel cell to calculate something. Sorry.

I used Saintly Loser’s formula, and changed the A1 to the actual cell with the address, and Excel 2013 hates it. It brings up a dialog box that “warns” me that it looks like I’m using a formula. It thinks that is not a command it can follow after an = sign. I’m lost again. And I do need to keep the rest of the address and not just keep the ZIP.

12

It should work. I tried it in a spreadsheet myself and it worked.

Couple of thoughts. . .

The formula is =LEFT(A1; LEN(A1)-5)

Where A1 is the cell with the address. Make sure you change the “A1” twice – you see it occurs twice in the formula.

Formulas have to start with “=”

Finally, maybe thelurkinghorror is right. Try changing the semicolon to a comma, see if that works. The semi works for me, but I see that Excel help shows it as a comma.

13

thelurkinghorror is right. The semicolon vs. comma thing is option. I have mine set to semicolon, but I think the default is a comma.

14

This shouldn’t be difficult.
Let’s say that you have the following in cell A1:


123456789

In cell B1 you could type the following


=LEFT(A1,LEN(A1)-5)

Cell B1 should return “12345”

If your entire list was in column A then you could copy down from B1 to get the conversion for the whole list. The easiest way to copy down is as follows: 1. click on B1 with the mouse 2. position the mouse on the bottom right of the cell on the little square. The cursor should change to a black cross. 3. double click.

15

It was indeed the comma problem. Thanks!!!

16

Why wouldn’t you want the full 7 correct zip code on their business cards?

17

It makes for a more matched set of lines in the address, and more room for a logo.

18

Whenever transferring data from one application to another, you should account for non-visible variations.

I think the equation I use for parsing any data transferred to an MS document via Excel is:
=Clean(Trim(Substitute(‘Cell Reference’, Code(160), Code(32)))

That takes care of 99% of parsing errors.

19

are your addresses consistent? Do some have a “-” and some not?

20

can you walk us through that?