Addresses - truncate 11 digit zip codes and leave 5 digit zip codes
Hello all,
I have a spreadsheet with 15000 addresses in a column and these addresses have both 5 digit and 11 digit zip codes. I would like a column with all 5 digit zip codes.
I currently use the formula =LEFT(A1,LEN(A1)-6) to convert 11 digit zips into 5 digits but I would like a formula that first checks the length of the zip code before performing the function.
Thank you for you time
Zip Code Sample.xlsx
basically
Please Login or Register to view this content.
if right 11 characters has has spaces then take whatever is in "A"
otherwise use your left len formula
Last edited by humdingaling; 07-25-2013 at 09:50 PM.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Incredibly quick response!!!!
I greatly appreciate it.
not a problem
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Another one, but slightly shorter
=IF(COUNT(RIGHT(A2,11)),LEFT(A2,LEN(A2)-6),A2)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks