# What's wrong with my Excel macro? **URL:** \ **Category:** Factual Questions\ **Created:** [October 17, 2013, 10:34pm UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529 "2013-10-17T22:34:43Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [October 17, 2013, 10:34pm UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/1 "2013-10-17T22:34:43Z")
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 ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 17, 2013, 11:26pm UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/2 "2013-10-17T23:26:26Z")
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. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 17, 2013, 11:37pm UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/3 "2013-10-17T23:37:00Z")
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. . . ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 17, 2013, 11:42pm UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/4 "2013-10-17T23:42:32Z")
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. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [October 18, 2013, 12:15am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/5 "2013-10-18T00:15:27Z")
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. ```auto Sub FiveZip() If Len(ActiveCell.Value) > 5 Then ActiveCell.Value = Left(ActiveCell.Value, Len(ActiveCell.Value) - 5) End If End Sub ``` ---
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [October 18, 2013, 1:44am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/6 "2013-10-18T01:44:21Z")
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. ---
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [October 18, 2013, 1:51am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/7 "2013-10-18T01:51:09Z")
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. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 18, 2013, 2:16am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/8 "2013-10-18T02:16:16Z")
> [@Cardinal](#): > > 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. 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. ---
**Author:** ![thelurkinghorror](https://avatars.discourse-cdn.com/v4/letter/t/7c8e57/32.png) [@thelurkinghorror](https://boards.straightdope.com/u/thelurkinghorror)\ **Post date:** [October 18, 2013, 2:20am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/9 "2013-10-18T02:20:22Z")
You’re comfortable with coding, but not formulas? Part of my job is [VB.NET](http://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). ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 18, 2013, 2:28am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/10 "2013-10-18T02:28:27Z")
> [@thelurkinghorror](#): > > **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). No, a semi-colon, at least in Excel 2010. It works for me – just checked. ---
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [October 18, 2013, 2:49am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/11 "2013-10-18T02:49:54Z")
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. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 18, 2013, 2:56am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/12 "2013-10-18T02:56:16Z")
> [@Cardinal](#): > > 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. 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. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 18, 2013, 3:02am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/13 "2013-10-18T03:02:05Z")
**thelurkinghorror** is right. The semicolon vs. comma thing is option. I have mine set to semicolon, but I think the default is a comma. ---
**Author:** ![j\_sum1](https://avatars.discourse-cdn.com/v4/letter/j/8baadc/32.png) [@j\_sum1](https://boards.straightdope.com/u/j_sum1)\ **Post date:** [October 18, 2013, 3:10am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/14 "2013-10-18T03:10:47Z")
This shouldn’t be difficult. Let’s say that you have the following in cell A1: ```auto 123456789 ``` In cell B1 you could type the following ```auto =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. ---
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [October 18, 2013, 3:27am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/15 "2013-10-18T03:27:18Z")
It was indeed the comma problem. Thanks!!! ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [October 18, 2013, 6:25am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/16 "2013-10-18T06:25:21Z")
> [@Cardinal](#): > > 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. Why wouldn’t you want the full 7 correct zip code on their business cards? ---
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [October 18, 2013, 11:34pm UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/17 "2013-10-18T23:34:01Z")
It makes for a more matched set of lines in the address, and more room for a logo. ---
**Author:** ![j666](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@j666](https://boards.straightdope.com/u/j666)\ **Post date:** [October 19, 2013, 2:38am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/18 "2013-10-19T02:38:18Z")
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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [October 19, 2013, 5:25am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/19 "2013-10-19T05:25:20Z")
> [@Cardinal](#): > > It makes for a more matched set of lines in the address, and more room for a logo. are your addresses consistent? Do some have a “-” and some not? ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [October 19, 2013, 5:26am UTC](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529/20 "2013-10-19T05:26:58Z")
> [@j666](#): > > 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. can you walk us through that? [Next page](https://boards.straightdope.com/t/whats-wrong-with-my-excel-macro/671529.md?page=2)