Shortening ZIP Codes

Hi Willy,
Yeh, probably my post was too long .. Lol.. often happens to me. But it is difficult to get enough information across to help someone understand. I thought I noticed you yesterday viewing some of my posts at ExcelForum. Rick was there as well, but not sure what he was looking at.
In some of my posts there I tackle the things we discussed here in a bit more detail, and with graphical explanations which help a bit.

But anyway I think we have it both fairly well in the bag now.

It was great that Rick popped by. I really appreciate that. Getting another insight into his thinking was very useful. I see now, for example, his reason for the Replace. I notice now in many of his codes that he has done similar things to reduce multiple concatenations. I find the insights into how people like Rick think incredibly useful.

I was very glad / relieved that he thought the If({1},___ ) was OK. I was nervous about that I had suggested it to you as I had not seen it done in this sort of code line before.
I expect that will be my standard now in place of where I usually did the standard ones like
Index(___ , 0 , 0 )
If(Row(), ___ )
If(Column() , ___ )
..etc...
Initially it seems to work in all the cases where I have used the other alternatives.
My thoughts and theories on these things suggested it should, so that is a relief ! .. Lol...
Have a great Easter, Willy, you too Rick if you ever pop by again, and many thanks again for that great enlightenment
Alan

P.s:
_.....”... logical thinking powered by urgent tendency of simplifying things,,,,” I can identify with that one :-)

Hi Rick,
Thanks for the compliment. It all started with finding it strange to replace an empty cell with an empty string. So I tried to replace IF(@="""","""",___ with IF(ROW(@),___ because ROW always returns an positive number (TRUE) and it worked. Then I experimented by removing the @ and it still worked (at that moment I didn't know why). So there was now only one place where the @ had to be replaced which gave me the idea to insert the Selection.Address directly into LEFT and discarding the Replace which resulted in my final code:
Selection = Evaluate("IF(ROW(),LEFT(" & Selection.Address & ",5))").
So it's just a matter of logical thinking powered by my urgent tendency of simplifying things :-)

Hi Alan,
As for your failing post, perhaps the reason is its length? Anyhow, I read it on box.com and found it very interesting although I must admid that some parts are beyond my level. Thanks to you though I got deeper into the matter and now have a better insight of arrays.

Hi Rick, Hi Willy,
I tried to post a reply to Rick’s last Post but it would not post: So for now I have dropped it off here:
https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-17.html#post4629001
and in another spare Forum post.
I also have it in a text file here:
https://app.box.com/s/oz2oga0ym6bue2kqf8assy8uq8qdod0e
I will try to post later again if I can
Alan

I did get your email message Alan....

First off, my apologies to Alan for my causing him many frustrating hours that he has spent pulling apart my one-liners. Second, kudos to Willy for finding that shorter one-liner. As it turns out, the code line can be made even shorter...

Selection = Evaluate("IF({1},LEFT(" & Selection.Address & ",5))")

Since there has been quite a lot of discussion about my code and the use of the Evaluate function, let me talk about this for a moment. First, it is not my intention to confuse things with my use of the Replace function and the @ symbol (I have also used the # symbol as well). I use Replace in order to avoid multiple concatenations and I do that in order to make it easier (for me at least) to construct the formula argument for the Evaluate function directly as I write it. My original code line was this...

Selection = Evaluate(Replace("IF(@="""","""",LEFT(@,5))", "@", Selection.Address))

I use the @ symbol as a stand-in for an address that I either know (as in this case) or have calculated earlier. I find it easier to create the formula that way then not using the Replace function which would require multiple concatenations like this...

Selection = Evaluate("IF(" & Selection.Address & "="""","""",LEFT(" & Selection.Address & ",5))")

Now it is true that Willy's, and the above, shorter code lines reference the address only once making concatenating the address directly into the formula argument less objectionable. By the way, when the address the output is going to is different than the address being evaluated, I tend to use the full address with a # sign replacing the row number for the end of the range instead. For example, if instead of using a Selection, the range had been fixed at A1 down to the last data item in Column A, and the output was to go to Column B (leaving the original data intact, then I would have written my original code line this way...

Range("B1:B" & LastRow) = Evaluate(Replace("IF(A1:A#="""","""",LEFT(A1:A#,5))", "#", LastRow))

instead of this way...

Range("B1:B" & LastRow) = Evaluate(Replace("IF(A1:A" & LastRow & "="""","""",LEFT(A1:A" & LastRow & ",5))", "#", LastRow))

I just find it easier to construct the formula part of the Evaluate argument without having to work around multiple concatenations. Of course, the above shorter version of the code could have been used to do this as well...

Range("B1:B" & LastRow) = Evaluate(IF({1},LEFT(A1:A" & LastRow & ",5))")

Now lets discuss a little bit about how the Evaluate function itself works. The natural tendency would have been to write the original code line this way...

Selection = Evaluate("LEFT(" & Selection.Address & ",5)")

but that does not work. Why? Underneath it all, the Evaluate function is an array-processing function and works just like an array-entered worksheet formula does except you do not have to commit the formula using CTRL+SHIFT+ENTER the way an array-enterd formula has to be committed... the Evaluate function, in effect, does that automatically for you behind the scenes. And that is the beauty of the Evaluate function... it returns an array of results which you can use VB code to manipulate... this gives us a tremendous amount of power as the underlying Excel formula processing engine that the Evaluate function taps into is quite fast. So, back to that last code line (where we try to process the LEFT function by itself). As it turns out, none of the text string processing functions appear to be array aware on their own, so you cannot get Evaluate to directly return an array of values from a text string function; however, if you embed that text string function within another function that is array aware, then it seems to induce array awareness in the text string function. That is where the IF function comes in... we just need it to process an array of values which, in turn, induces the text string function to produce an array of values to the IF function which, in turn, returns an array of values to the Evaluate function. Willy's ROW() function call does that as does the single element array constant ({1}) that I posted above. My usual fallback for the IF function used this way is to test for the iterated cells in the array for being empty and, if so, to return the empty string... usually this suppresses zeroes from being returned by the Evaluate function when an empty cell is encountered; however, since the LEFT function naturally returns an empty text string when the value it is processing is empty, that test for emptiness was not required (which is why ROW() or the single-element array constant that Willy and I proposed could be used).

Hi Alan,
A while ago I tried with 1 and TRUE but of course that doesn't work, not being an array. Putting 1 in curly braces is a very clever solution. It feels like fooling Excel and I like it.

Willy

P.S. It's a good thing keeping learning.

Hi Willy,
I am glad you looked into this, it turned out to be good learning. .

As far as I can see you have the answer, and I cannot see any extra advantage in this case of Rick’s alternative. ( His would return an empty explicitly for an empty cell, whereas yours would do so indirectly as the formula LEFT(____,5) will not error if the cell is empty , - it will return an empty again. So in effect they do the same )

I agree I would go finally with your solution, unless I felt like arranging that if the cell was empty , then the formula would write words to that effect in the cell.

=IF($A$1:$A$10="","NuFinkThereMate",LEFT($A$1:$A$10,5))

_.....

Seeing and understanding the other alternatives is, as we have seen, a great way to learn.
Shame Rick did not stop by. I dropped him an Email, but he is very busy helping and answering in Forums
Alan

P.s. I was just using another trick in a Forum answer_..
https://www.excelforum.com/excel-formulas-and-functions/1180282-average-of-data-excluding-weekends-holidays-and-zeros-2.html#post4626920
_.. that one would allow you to simplify your formula a little further:

= Evaluate("=IF({1},LEFT(A1:A10,5))")

Hi Alan,

You are right, Rick's solution as well as my variants work practically equally well, even Allan Wyatt's loop does the job just fine. But trying to simplify code is a hobbyhorse of mine: "simplicity is the ultimate sophistication" (Leonardo da Vinci). For me, making a macro to do a job is the first step. Once it works I always try to simplify it :-)

The Watch Window is quite an interesting tool. I rarely use it because most of the time I can see the result in a variable in the tooltip by hovering over it with the mouse cursor but I didn't know you can analyse arrays in this way. I must remember this :-)

Willy

Hi Willy,
Yep , I spent a bit of time trying to figure out what the “@” was about as well!!! – I was thrown off, as @ is a Wildcard thing used in a Find replace Thingy. I should have guessed though , - as Rick often uses unusual character for no particular reason. I always make a point of trying to make it obvious what is going on, hence my alternative of “AnyFinkULike” .. Lol..


“.....I was thinking that instead of first inserting the parameters and then replace the value in the cell by the result it would be more direct....”........I may have misunderstood what you are saying there. You and Rick in fact are doing almost exactly the same. If you follow the order of things as I explained in my last post , then you will see that Rick is just doing that Replace stuff to get this final formula to evaluate:
IF($A$1:$A$10="","",LEFT($A$1:$A$10,5))
That is quite close to yours.
This is a bit closer still, a sort of “half way house” between Rick’s and yours:
IF(Row(($A$1:$A$10),LEFT($A$1:$A$10,5))
Rick is , again, just making it look more complicated then it is or needs to be. Your formula_..
IF(Row((),LEFT($A$1:$A$10,5))
_.. does exactly the same


“...."IF(ROW(),___ is working without the reference parameters to the array because Excel seems to be tolerant sometimes. Obviously ROW()'s array "follows".......” .....That is more or less what all my experiments suggest. In some cases it can be a bit more complicated. I have some very long detailed Blogs on it. I have never seen anyone else tackle this theme definitively in detail.. , so I did.

Very briefly, and a bit simplified:.. - once multi value analysis kicks in , then the following rule applies: Excel will keep going, that is to say keep returning values in an output Array to match the size and orientation of the largest dimensions of all Arrays and / or ranges in the total formula.

So for example, if you change your solution slightly to this_.....

Sub MultiValueFromSingleValueFunctionStuff() ' https://excelribbon.tips.net/T010768_Shortening_ZIP_Codes.html
Dim Arr() As Variant
Let Arr() = Evaluate("IF(Row(A1:A11),LEFT(A1:A10,5))") ' Run code in Debug, F8 mode, stop at End Sub. - Highlight either Arr - Hit Shift+F9 - OK You will see in Watch window that Arr is a (1 to 11, 1 to 1) array. The last value is an error
Let Range("B1").Resize(UBound(Arr(), 1), UBound(Arr(), 2)).Value = Arr() ' Increase size of range to match that of array and assign the array values to the range
End Sub

_.. you will see that Excel has tried to do the evaluation over 11 “rows”. The last value returned in Arr(11, 1) will be an error as you had no value available in LEFT(A1:A10,5) when it did its 11th run.

Alan

@Alan: "The original code from Rick looked a bit more complicated then it was, or at least I was mistakenly reading into it more than was there."
I entirely agree with you and it had the same effect on me. Now that I finally "got it" it is in fact simple but I was "distracted" all the time by this @ which I thought could have a special meaning.

But while trying to decipher Rick's method recently (again :-), I was thinking that instead of first inserting the parameters and then replace the value in the cell by the result it would be more direct to replace it right away. So I came up with the following:
Selection=Evaluate("IF(ROW(),LEFT(" & Selection.Address & ",5))")
and it worked just fine.
"IF(ROW(),___ is working without the reference parameters to the array because Excel seems to be tolerant sometimes. Obviously ROW()'s array "follows" the array of the main element in the IF function: LEFT(SomeAddress,5) so it isn't necessary to add the reference range parameter to ROW().

Hi Willy,
Thanks I get it , - I expect thanks to you...
It is always difficult to get things across in the confines of a Comment post.
I expect we may be saying the very same thing.
I was thinking that Rick was doing something more clever with a complete address, and changing a 9 number zip to a 5 number zip and maintaining the full address. That was wrong. You are correct – Rick’s code is doing exactly what Allen’s is. ( And as often, preparing a comment or Forum post often results in you solving the problem yourself.. LOl.. – The “Power of Posting” !!! - I swear by it, ( and occasionally swear when I don’t get it either.. lol.. ) )

What his code actually does is simply returns the first 5 characters of a Selection just as you say.

This is my explanation, and I expect it is exactly what you are saying as well ...

Within the VBA Evaluate method argument, you may include VBA things. These things will be done ( evaluated but not by the Evaluaet(“ “) , rather just by VBA ) , first.
The result they give will then become part of the final string that VBA finally evaluate.

Also, even in a “one liner” , VBA code, things will actually be done ( evaluated ) in a pre determined order. ( Arguments within a Function , for example, which are themselves a Function or formula or VBA things will, be evaluated first if need be )

So this is Rick’s code progression

_ 1. Selection.Address is evaluated first - - In your suggested example it returns $A$1:$A$10

_ 2. The Replace is then done in this form:
Replace("IF(AnyFinkULike="""","""",LEFT(AnyFinkULike,5))", "AnyFinkULike", “$A$1:$A$10“)
so we will then have that Replace Function returning us this:
"IF($A$1:$A$10="""","""",LEFT($A$1:$A$10,5))"

_.3. The Evaluate(“ “) is now done on that last string above
The main important thing that is done in that string is
LEFT($A$1:$A$10,5)

As I expect you have gathered , these sort of simple functions like the VBA Strings Left Function, can be given a multi value argument instead of the usual single value. Then, whilst having “available in them” a number of values to be evaluated, they will not usually do multivalent evaluations.
We need some trick, that is to say we add something to the total formula which makes multi value evaluation take place, but which does not affect the overall result.
At each multi value evaluation a different value from those “available in them” is taken. Correspondingly the output is returned in an array which is dimensioned to reflect the evaluations being gone through in the conventional multi evaluation order of “along all columns then down to next row” ( In our case for a single column it just “goes down the rows”)
The trick I think you have used somewhere was the If(Row(),____)
Rick is suing a slightly different one
If(IF($A$1:$A$10="""","""",_____)
Your trick will give an array output over a dimension equal to the range in LEFT($A$1:$A$10,5), which will be (1 to 10, 1 to 1 ).
Ricks will give an output over a dimension equal to whichever is the bigger of the range in LEFT($A$1:$A$10,5) or the range in If(IF($A$1:$A$10="""","""",_____) , which in this example is actually the same. So similarly, a (1 to 10, 1 to 1 ) array is returned.

I think we both got it eventually. Just as you say, Ricks final code is just doing exactly what Allen’s code does

The original code from Rick looked a bit more complicated then it was, or at least I was mistakenly reading into it more than was there.

Alan

@Alan
I am also not familiar with US ZIP codes but that is not important. Just fill some cells with 5 and 9 digit numbers, that's were it's about

In my opinion Rick's line of code does exaclly the same as Allen Wyatt's loop because, if for example A1:A10 is selected, the immediate window evaluates it to an array formula:
IF($A$1:$A$10="","",LEFT($A$1:$A$10,5))

But while composing this comment it suddenly became clear to me.
In Rick's code:
1) "IF(@="""","""",LEFT(@,5))" is the "expression" part of VBA's Replace function.
2) "@" is the Substring being searched for
3) Selection.Address is the Replacement substring
and voilà :-)

@Willy
Hi, I might possibly have an idea what is going on..
But the problem is I have no idea about the United States, ZIP Codes and typical house addresses. Can you give me a few complete addresses. Make them up if you like, but give some with the typical five digit and some with the typical nine digit ZIP code. Make sure some of them are exactly the same address, but in the different five or nine digit ZIP code convention.
I have a feeling Rick is doing something a bit different to what Allen Wyatt was doing. But I need some typical data to check.
Thanks
Alan

@Alan
The immediate window is a very usefull tool. When I did what you suggested I get the expected result. But unfortunatly it doesn't work all the time. When I do it with both my slightly shorter alternatives, I get a runtime (13) error when i press enter on ?strEval. But when I use this code in a macro, it works perfectly.

When I use Rick's code line I get for example:
{=IF($A$1:$A$10="","",LEFT($A$1:$A$10,5))}
But that doesn't explain how this result is generated by:
Replace("IF(@="""","""",LEFT(@,5))", "@", Selection.Address)
The @ has no special meaning. You can replace it with any character or word, even numbers.
???

@Willy
Your welcome. I have spent many frustrating hours pulling apart Rick’s one liners in order to understand them, especially the Evaluate(“ “) ones
The best tip is to look at the final string used in the immediate window.
Like do this:
strEval=Replace("IF(@="""","""",LEFT(@,5))", "@", Selection.Address)
Debug.Print strEval
‘ Hit Ctrl + g from the VB editor Window to bring up the Immediate window
The string you see in the Immediate window is then exactly as you would type physically into a cell , ( sometimes the = is missing as you can leave that out when doing a formula in Evaluate(“ “) , so you would then need to include a = when typing into the cell )
Alan

@Alan
Interesting stuff. Thanks.

@ Willy
Here is some stuff that might help you with understanding of theose sort of code lines..
https://usefulgyaan.wordpress.com/2013/06/19/avoid-loop-for-range-calculations-evaluate/
https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-12.html#post4558827
You also need to get a good feel for using quotes in Evaluate when trying to go through some of Rick's Evaluate one Liners
https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-11.html#post4555023
https://www.excelforum.com/excel-programming-vba-macros/1177586-for-loop-to-iterate-cell-row-in-a-formula.html#post4605832
Alan

@Rick Rothstein
Here is even a shorter one-liner that works equally well:

Sub ZIPShorter()
Selection = Evaluate("IF(Row(),LEFT(" & Selection.Address & ",5))")
End Sub

@Rick Rothstein

I like one-liners but I don't understand quite well how this one works. Anyhow I have been experimenting and came up with a slightly shorter version which seems to work equally well:

Selection = Evaluate(Replace("IF(row(@),LEFT(@,5))", "@", Selection.Address))

I guess the @ acts like some place holder for the Selection.Address array but the arguments you use don't match the usual syntax of Replace at all. Can you elucidate?

Thanks.

You can write the macro version without using a loop...

Sub ZIPShorter()
Selection = Evaluate(Replace("IF(@="""","""",LEFT(@,5))", "@", Selection.Address))
End Sub

I see that part of the procedure in the first solution presented involves converting the =LEFT(*,*) formulae in column H to values without moving them around.

A simpler way of accomplishing this formulae-to-values-in-place step I often use is, after selecting the formulae:

(1) Set the mouse somewhere on the border of the selection (the mouse pointer changes from a bold cross to a bold upper left-pointing arrow with four additional smaller up/down/left/right arrows).

(2) Right-click and then drag the selection to some other nearby area, then back again to its original area, all while keeping the right-click active. You can go as far away as you like before dragging back "home", but even just one column away will do (or one row away if your selection happens to not be an entire column as is the case in the given example).

(3) Now release the right-click and a multi-option contextual menu appears, one of whose options is "Copy Here as Values Only". Click this option (or use V as the hot key) and you're done.

You must do the drag-away-and-back step to make this contextual menu appear; it won't work by simply right-clicking on the border alone.

You can also use this procedure to simultaneously move the selection to a different area and change all formulae within it to values. You need not drag the selection back to its original location — although in this case the original formulae will persist where they were first created and may no longer be of any value, especially if the move is into cells used by the formulae. The user must decide for themselves what to do afterward with the formulae cells. If the move was indeed into input cells then the formulae will likely no longer have correct results and should be deleted.

This procedure works on my copy of Excel 2007; I can't confirm for other/newer versions and will leave this for others to verify.

Great tip on how to remove +4 extension from zip code column. Have a list of over 200,000 lines that had to be formatted correctly, this did it. Much better help than Windows MS Excel help.