Separating part of post-code in address

sclark

New Member
Joined
Nov 21, 2005
Messages
1
How do I separate out the first 5 digits of the post code if I have addresses like these all in one cell? I can't figure it out using Left, Right or Mid functions.

ALPHARETTA GA 30023-2818 ---> needs to be 30023
ALBION CA 95410-0760 ----> needs to be 95410
BOCA RATON FL 33431-4227 ---> needs to be 33431
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,920
Try...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),5)

Hope this helps!
 
L

Legacy 51064

Guest
If all of your information is in the same format:

A1:
ALPHARETTA GA 30023-2818


B1:
=MID(A1,FIND("-",A1)-5,5)
 

Forum statistics

Threads
1,077,829
Messages
5,336,647
Members
399,094
Latest member
Learner2019

Some videos you may like

This Week's Hot Topics

Top