Sorting Directory City/Street/Number

M

Murph

I have a huge list of last name, home number, street and city
My goal is to separate the info into:
Street Name
House Number Last Name

i.e.
HIGGINS 1173 Birch Bark Ct
ARBINO 1177 Birch Bark Ct
SCHLOEMER 1185 Birch Bark Ct

could appear as:

Birch Bank Ct
1173 HIGGINS
1177 ARBINO
1185 SCHLOEMER

Thoughts?
 
A

Ashish Mathur

Hi,

Use this for the numbers. E4 contains the text string

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

Use this for the name

=LEFT(E4,SEARCH(" ",E4))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top