How do I do a Reverse Find/Search in Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to parse addresses into their basic components (I.E. "123 Main
Street" becomes "123", "Main", "Street") in an Excel Spreadsheet, for later
conversion into a Relational DB.

If H2 = 123 Main Street
To get the Address number (D3): TRIM(MID(H2,1,FIND(" ",H2,1)))
To get the street name (E3):TRIM(MID(H3,LEN(D3)+1,FIND("
",H3,LEN(D3)+1)+LEN(D3)+1))
To get the street type (F3): TRIM(MID(H3,FIND(E3,H3)+LEN(E3),LEN(H3)))

My problem is when the street name is two words (North Main Street). I get
a street name of North and a street type of Main Street. It is also possible
that the street may have a 3 word name.

My first qusetion is how can I do a search starting from the right (RIGHT
function does not do it) to find the last word in the string?

Also, is there a cleaner way to parse the strings than I have done?

Thanks for you help.
 
This formula will extract the last word in a string when there is a space
before the last word

=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

with string in A1

I see nothing wrong with your parsing, there are things you can do after you
have extracted a string, you can use substitute to eliminate that string
from the rest and just use the cell where you parsed that string
Parsing is notoriously difficult in Excel and there are always exceptions to
name rules that it will choke on

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
Peo,

Thanks for the code. I'm going to sit down with it and learn how it works
(after this is done).
 
Peo Sjoblom's formula produces an error. Here's a correct formula (that assumes you don't use "|" in your strings):

Code: 
=IF(ISERROR(FIND(" ",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))
 
Back
Top