I have a list if ~60,000 zip codes in column T. Example as follows:
90025
10022-3556
12456
50223
60036-9944
77533-6655
34775
I need remove all the characters to the right of the "-' character, including the "-". The desired output is as follows:
90025
10022
12456
50223
60036
77533
34775
I could use this formula in column U, but I need to leave the structure of the file intact. Is there a way to do this with VBA? Thanks
Please Login or Register to view this content.
Try =IF(ISERROR(LEFT(T2,FIND("-",T2)-1)),T2,LEFT(A1,FIND("-",T2)-1))
I wasn't sure if you are using 2003 or 2007 +, so my formula is based on Excel 2003 version
Hi maacmaac,
select the data, press Ctrl + H and replace -* with blank (nothing).. thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Wouldn't it be just: =IF(LEN(T2)>5,LEFT(T2,5),T2) and then Copy Paste Special values on top of the T column and delete the formula column entries.
Last edited by Cutter; 04-27-2012 at 02:39 PM. Reason: Added IF() to formula
Please Login or Register to view this content.
Hello maacmaac,
Here is a flexible and fast VBA solution to your ZIP code problem. The range is automatically sized and read into an array. The array values are modified using Regular Expressions. The modified array is then copied back into the range.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
This should work:
I hope it helps.Please Login or Register to view this content.
Last edited by Pichingualas; 04-27-2012 at 02:44 PM.
.?*??)
`?.???.?*??)?.?*?)
(?.?? (?.?Pichingualas <---
??????????????????????????
Wrap your code with CODE TAGS.
Thank those who helped you, Don't forget to add to their REPUTATION!!! (click on the star below their post).
Please mark your threads as [SOLVED] when they are (Thread Tools->Mark thread as Solved).
Hi maacmaac
Another approachPlease Login or Register to view this content.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
As DILIPandey suggested, but using VBA. (Change column reference as needed.)
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks