• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding Hyphen between numbers

Prinz Jaymes

Prinz Jaymes

Member
Hello
I have a ton of customer data that I export from Access; however, whenever I export USA zip codes into Excel, they have no hyphens , and I manually have to add them. Is there a way to make this easier.

For instance
City, State, Zip
RED BLUFF CA 960809381 (Need to convert this to show 96080-9381
RED BLUFF CA 96080 (this is fine)
Is there a formula I can run down an entire column to convert "only the ones that need hyphens?"
 
oops...

=IF(LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))>5,TEXT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),"00000-0000"),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))
 
Hi Somendra
Basically, any number with 9 digits must have - after 5th digit. Any number with 5 digits should remain unchanged
 
Alternateivly, if you don't want to end up changing some values to numbers and some to text, you can change the format of the original cells to have a custom number format of:
[>=100000]00000-0000;00000
 
Deepak
Your approach makes the formula process look like ABC. I like it, and actually did apply it in another report. Thank you for your priceless input!!
 
You must log in or register to reply here.
Back
Top