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

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




"NumberDocc" <[email protected]> wrote in message
news:[email protected]...
>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.
>
>

Peo,

Thanks for the code. I'm going to sit down with it and learn how it works
(after this is done).

"Peo Sjoblom" wrote:

> 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
>
>
>
>
> "NumberDocc" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >
> >
>
>