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&"01234567 89")))-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
"Murph" wrote in message
...
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?