I have a list of about 8000 addresses. Unfortunately, the address field included the street, city, state and zip. The state and zip were relatively easy to extract since they were set character lengths (zipcode was 5 character and state was 2). Now what I'm left with is a string that includes the address and city.
Lastly, to extract the city, I took the remaining address data and ran a "text-to-columns". As a result, I was able to extract most of the cities, but not all of them. And now this is my dilemma.
Unfortunately, there is no space between the "last character of the address" and the "city" for almost 4000 of the records, therefore I have character strings similar to the following:
1234 Your Street Ste 104Houston
5678 My Street Building 2San Diego
91011 Our Street NWNew York
With 4000 records, it would be quite a manual exercise to extract the cities from the addresses. Does anyone have any idea of how to extract the cities from these character strings?
ONE IDEA: Many of the cities have numerical characters next to them as in the first two examples above. Is there a way to search a text string to find the rightside characters UP TO a numerical character. This would at least get me much further along. Any ideas?
This will remove from starting to last numeric.Originally Posted by kwj1966
=REPLACE(A1,1,MATCH(10,INDEX(--MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),0)),"")
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
Assuming city names are no longer than 20 chars... then try this to extract from the ones that have the number as you describe in 1st 2....
Assuming first string in A2:
In B2: =RIGHT(RIGHT(A2,200),20-MIN(FIND(0,SUBSTITUTE(RIGHT(A2,20),{1,2,3,4,5,6,7,8,9},0)&0))+1) copied down
In C2: =SUBSTITUTE(B2,LOOKUP(9.999999999E+307,--("0"&MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0 123456789")),ROW($1:$30)))),"") copied down to get city, you can then copy and paste special >> Values and fill in the blanks...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Do you have a list of cities? If so then with data in A2 down try this formula in B2 down
=RIGHT(A2,MATCH(2^15,INDEX(MATCH(RIGHT(A2,ROW(INDIRECT("1:20"))),CityList,0),0)))
That will find the first match in the CityList looking at the last 20 characters of A2, then the last 19, last 18 etc. all the way down
Audere est facere
..And another option.
In B1.
=TRIM(MID(A1;LEN(LOOKUP(9^9;LEFT(A1;COLUMN(1:1))*1))+1;2^15))
Change semi-colons bt gomma if you have to do it..
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks