Zip code long and short

[daniels012]

I have in cell B7:
Raleigh, NC 27628
I use this in cell C10 to get only the zip code:
=right(mid(B7,1,find(",",B7,1)+10),5)
This works great for 5 numbers.
What can I add to my code to look for either a normal 5 number zip or 9 number zip?
ie: 27628 or 27628-6427

Thank You,
Michael

 

[Von Pookie]

This is working for me:

=RIGHT(B7,LEN(B7)-FIND("~",SUBSTITUTE(B7," ","~",LEN(B7)-LEN(SUBSTITUTE(B7," ","")))))

 

[daniels012]

That works perfectly!! I really don't understand the ~ squiggly mark in the equation... But it works perfect!

Thank You,
Michael

 

[Von Pookie]

Basically it's replacing the last space in the text string with the "squiggly mark" and then using that to find the starting point of the zip code.

You're welcome