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

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.
 
P

Peo Sjoblom

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
 
G

Guest

Peo,

Thanks for the code. I'm going to sit down with it and learn how it works
(after this is done).
 
Joined
Aug 28, 2012
Messages
12
Reaction score
0
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," ",""))))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top