Helper Column dropping leading zeros

[leetmilf]

There are hundreds of topics on leading zeros - I've searched several and can't quite get the answer I am looking for - so I apologize in advance for potentially repeating a topic.
A few months back I posted a similar topic but the answers do not apply in this situation.
I'm making a zip +4 to locality tool. Basically - each zip code returns a locality of 1 or 99. However, there are certain zip codes that require zip+4 to get the actual locality. I've created a simple tool for processors to enter the zip code. Below notice the function for the 2014 tool:

If the locality returns #N/A they are to then enter the +4. (I will soon put a conditional formatting on there to further reduce confusion, but for now this is as far as I've gotten)
I have the 2015 tool ready to go(as far as I know - anyway...no truncated leading zero situations)
the 2014 tool is giving me issues.

I used a helper column to match the zip code and plus 4 cells. I've tried everything - and the helper column is dropping leading zeros from the PLUS 4 cell.:

I've tried custom formatting, apostrophes, converting fields to text, converting fields to zip code, etc. I'm about to throw my computer out the window, seriously. How do I get the helper column to KEEP the leading zeros? I have about 38k rows and I honest do not want to go through adding 0's, because the next file I make is for another dept and includes all 50 states....this is just for the state of MI.

 

[MARK858]

I don't get the same issue but I suppose you could try

=A6674 & "#" & TEXT(I6674,"0000")

but just as a side question what is your formatting set as for both columns I and L and what does your formula bar show for I6674?


BTW try and use screenshots that are copyable rather than images (most of us don't want to retype data and will normally just skip the posts).
See my signature block for some ways to post usable screenshots.

 

[leetmilf]

I don't get the same issue but I suppose you could try but just as a side question what is your formatting set as for both columns I and L and what does your formula bar show for I6674?


BTW try and use screenshots that are copyable rather than images (most of us don't want to retype data and will normally just skip the posts).
See my signature block for some ways to post usable screenshots.

Thank you for the screenshot tip - I will be sure to use that next time!
My formatting for I was Special '0000 and my formatting for L was General
I resorted the sheet by the Plus 4 column, and used your formula for all rows that contained a Plus 4 entry - then changed it to accommodate for those that didn't. It's working fine now - thank you!

 

[MARK858]

Then it looks like was column I causing you the issue as you probably never really had leading zeroes in the cell, they appeared in the cell only because of the format (which is a bit strange as in your image it shows a leading ') which only changes the appearance not the underlying value.

To check click a cell in column I and see if what appears in the formula bar has leading zeroes or not.

 

[leetmilf]

Then it looks like was column I causing you the issue as you probably never really had leading zeroes in the cell, they appeared in the cell only because of the format (which is a bit strange as in your image it shows a leading ') which only changes the appearance not the underlying value.

To check click a cell in column I and see if what appears in the formula bar has leading zeroes or not.

After I applied your fix, I reformatted Column I as Special 0000 (without the leading ') Now when I click on the cell (column I 0030) the formula bar shows 30, however my helper column shows 48160#0030

 

[leetmilf]

A big thank you to everyone that replied with help on this thread. I applied suggested fixes and everything works. This also enabled me to properly format sheets prior to pasting data - which saved numerous frustrations in the long run (I made one for local claims department as well as our ITS department)
The only issue I had was file limits on email attachments - so the ITS file ended up being split in half for each year. Just adding the clear button macro to the ITS files and it gets turned in for approval tomorrow. You guys are *amazing*