Extract Text From A String

[wallstcheater]

I have thousands of address fields that look like this:

100 Broadway, Suite 1101 New York, NY?10005 United States

Sector 30, NH-8 Gurgaon, Haryana, ?122002

61 Broadway 17th floor New York, NY?10006 United States 11 Beacon Street, 3rd Floor Suite 305 Boston, MA?02108 United States 88 7th Avenue New York, NY?10109 United States 600 Pennsylvania Ave SE Suite 220 Washington, DC?20003 United States 1601 Elm Street Suite 3900 Dallas, TX?75201 United States 50 Federal Street Suite 600 Boston, MA?02110 United States <!--StartFragment--> <colgroup><col width="567"> </colgroup><tbody> <!--EndFragment--></tbody> I need to separate the street address, city, state, zip and country.

<!--StartFragment--> <colgroup><col width="567"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[Rick Rothstein]

I have thousands of address fields that look like this:

100 Broadway, Suite 1101 New York, NY?10005 United States

Sector 30, NH-8 Gurgaon, Haryana, ?122002

61 Broadway 17th floor New York, NY?10006 United States 11 Beacon Street, 3rd Floor Suite 305 Boston, MA?02108 United States 88 7th Avenue New York, NY?10109 United States 600 Pennsylvania Ave SE Suite 220 Washington, DC?20003 United States 1601 Elm Street Suite 3900 Dallas, TX?75201 United States 50 Federal Street Suite 600 Boston, MA?02110 United States <TBODY> </TBODY> I need to separate the street address, city, state, zip and country.

<TBODY>
</TBODY>

There is no reliable way to pull the city out of that text because there is no delimiter (comma) between the street and the city... without a full and comprehensive list of possible cities, there is no way for a formula (or VB code for that matter) to know if the city is made up of one, two or three words.

 

[marious]

I have thousands of address fields that look like this:

100 Broadway, Suite 1101 New York, NY?10005 United States

Sector 30, NH-8 Gurgaon, Haryana, ?122002

61 Broadway 17th floor New York, NY?10006 United States 11 Beacon Street, 3rd Floor Suite 305 Boston, MA?02108 United States 88 7th Avenue New York, NY?10109 United States 600 Pennsylvania Ave SE Suite 220 Washington, DC?20003 United States 1601 Elm Street Suite 3900 Dallas, TX?75201 United States 50 Federal Street Suite 600 Boston, MA?02110 United States <tbody> </tbody> I need to separate the street address, city, state, zip and country.

<tbody>
</tbody>

Mr. Rothstein is correct, but I used a different approach that might solve your issue for most us cities

1. Create a dictionary with possible cities or defined name with US cities. Google a list of US cities this site List of United States cities by population - Wikipedia, the free encyclopedia it took me 10 min top create the city dictionary with 278 rows
2. Notes :that CityUS has 278 cities you can add more cities if you see that they are going to be appearing but remember to increase the range of the formula, otherwise the lookup will not work. Use CTL+SHIFT+ENTER to enter the formula in column C
3. They still will be some inconsistencies and you will need to validate the final data set row by row typing any missing data. <title>Excel Jeanie HTML</title>
   
   
   <!-- ######### Start Created Html Code To Copy ########## -->
   
   
   String
   
   

   *	A	B	C	D	E	F
   2	100 Broadway, Suite 1101 New York, NY?10005 United States	100 Broadway, Suite 1101 *	New York	NY	10005	United States
   3	Sector 30, NH-8 Gurgaon, Haryana, ?122002	*	*	*	*	*
   4	61 Broadway 17th floor New York, NY?10006 United States	*	*	*	*	*
   5	11 Beacon Street, 3rd Floor Suite 305 Boston, MA?02108 United States	*	*	*	*	*
   6	88 7th Avenue New York, NY?10109 United States	*	*	*	*	*
   7	600 Pennsylvania Ave SE Suite 220 Washington, DC?20003 United States	*	*	*	*	*
   8	1601 Elm Street Suite 3900 Dallas, TX?75201 United States	*	*	*	*	*
   9	50 Federal Street Suite 600 Boston, MA?02110 United States	*	*	*	*	*

   
   <colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 495px;"><col style="width: 282px;"><col style="width: 169px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 90px;"></colgroup><tbody>
   </tbody>
   
   

   Spreadsheet Formulas

   Cell Formula B2 =SUBSTITUTE(LEFT(A2,FIND("?",A2,1)-3),C2&",","") C2 {=IF(SUMPRODUCT(IFERROR(--(FIND(CityUS,A2,1)>0),0),ROW($A$1:$A$278))=0,"",INDEX(CityUS,SUMPRODUCT(IFERROR(--(FIND(CityUS,A2,1)>0),0),ROW($A$1:$A$278)),))} D2 =MID(A2,FIND("?",A2,1)-2,2) E2 =MID(A2,FIND("?",A2,1)+1,5) F2 =MID(A2,FIND("?",A2,1)+7,LEN(A2)-FIND("?",A2,1)+7) <tbody> </tbody>

   Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

   
   <tbody>
   </tbody>
   
   
   Excel tables to the web >> Excel Jeanie HTML 4
   
   
   　
   
   　
   This is a link to the dictionary http://sdrv.ms/1827ncC

 

[wallstcheater]

There is no reliable way to pull the city out of that text because there is no delimiter (comma) between the street and the city... without a full and comprehensive list of possible cities, there is no way for a formula (or VB code for that matter) to know if the city is made up of one, two or three words.

Thank you for the response. At first it seems like a very easy problem. I searched the forum and tried a variety of solutions, but nothing seemed to work that well. Maybe I should pass it off to a co-worker and claim that I am too busy!

 

[wallstcheater]

Wow. This looks promising. I'm going to give it a try!