Sorting Directory City/Street/Number
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? |
Sorting Directory City/Street/Number
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? |
All times are GMT +1. The time now is 12:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com