I need to add 4 trailing zeros to a zipcode when the zipcode contains only 5 characters.
Data (includes null rows):
752045513
71862
770243928
75204
752054524
Expected Output (null rows should be left null):
752045513
718620000
770243928
752040000
752054524
Your assistance is appreciated.
I tried =TEXT(B3, "000000000") but it is adding leading zeros.... and putting zeros in the null rows.
I believe that the number of digits should be 9 so try the below formula.
Formula:
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
It didn't work for the rows with a 5 character zip........... other rows look fine.
Example: 75204 shows as 75204 instead of 752040000
if your data is in A1, use this...
=IF(A1="","",A1&REPT("0",9-LEN(A1)))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Oops mis interpreted it as leading zeroes.
Try this...
Formula:Please Login or Register to view this content.
or with Value( )
Formula:Please Login or Register to view this content.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
the formula by FDibbins and vlady also didn't add the trailing four zeros to the zipcode, but thank you for your efforts!
Sixthsense new formula works perfectly, thank you!!!!
We require the sample of the data to find the root cause of this problem. Can you please share that cell info in a dummy file as an attachment in continuation of this thread?
sixth sense, I updated my previous comment, your formula works perfectly, thank you!!!
hmmm. so there are leading/trailing spaces... the trim() did it...
ahhhh, ok thank you vlady!!!!!
Thanks for the feedback. Glad your problem is solved.
The solutions we offered are just based on assumption and sometimes we will be lucky to see it working and some time it may not…
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks