software

I am trying to parse addresses into their basic components (I.E. quot;123 Main
Streetquot; becomes quot;123quot;, quot;Mainquot;, quot;Streetquot;) 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(quot; quot;,H2,1)))
To get the street name (E3):TRIM(MID(H3,LEN(D3) 1,FIND(quot;
quot;,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(quot;^^quot;,SUBSTITUTE(A1,quot;
quot;,quot;^^quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;)))))

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

quot;NumberDoccquot; gt; wrote in message
...
gt;I am trying to parse addresses into their basic components (I.E. quot;123 Main
gt; Streetquot; becomes quot;123quot;, quot;Mainquot;, quot;Streetquot;) in an Excel Spreadsheet, for
gt; later
gt; conversion into a Relational DB.
gt;
gt; If H2 = 123 Main Street
gt; To get the Address number (D3): TRIM(MID(H2,1,FIND(quot; quot;,H2,1)))
gt; To get the street name (E3):TRIM(MID(H3,LEN(D3) 1,FIND(quot;
gt; quot;,H3,LEN(D3) 1) LEN(D3) 1))
gt; To get the street type (F3): TRIM(MID(H3,FIND(E3,H3) LEN(E3),LEN(H3)))
gt;
gt; My problem is when the street name is two words (North Main Street). I
gt; get
gt; a street name of North and a street type of Main Street. It is also
gt; possible
gt; that the street may have a 3 word name.
gt;
gt; My first qusetion is how can I do a search starting from the right (RIGHT
gt; function does not do it) to find the last word in the string?
gt;
gt; Also, is there a cleaner way to parse the strings than I have done?
gt;
gt; Thanks for you help.
gt;
gt;Peo,

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

quot;Peo Sjoblomquot; wrote:

gt; This formula will extract the last word in a string when there is a space
gt; before the last word
gt;
gt; =RIGHT(A1,LEN(A1)-SEARCH(quot;^^quot;,SUBSTITUTE(A1,quot;
gt; quot;,quot;^^quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;)))))
gt;
gt; with string in A1
gt;
gt; I see nothing wrong with your parsing, there are things you can do after you
gt; have extracted a string, you can use substitute to eliminate that string
gt; from the rest and just use the cell where you parsed that string
gt; Parsing is notoriously difficult in Excel and there are always exceptions to
gt; name rules that it will choke on
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Northwest Excel Solutions
gt;
gt; www.nwexcelsolutions.com
gt;
gt; (remove ^^ from email address)
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;NumberDoccquot; gt; wrote in message
gt; ...
gt; gt;I am trying to parse addresses into their basic components (I.E. quot;123 Main
gt; gt; Streetquot; becomes quot;123quot;, quot;Mainquot;, quot;Streetquot;) in an Excel Spreadsheet, for
gt; gt; later
gt; gt; conversion into a Relational DB.
gt; gt;
gt; gt; If H2 = 123 Main Street
gt; gt; To get the Address number (D3): TRIM(MID(H2,1,FIND(quot; quot;,H2,1)))
gt; gt; To get the street name (E3):TRIM(MID(H3,LEN(D3) 1,FIND(quot;
gt; gt; quot;,H3,LEN(D3) 1) LEN(D3) 1))
gt; gt; To get the street type (F3): TRIM(MID(H3,FIND(E3,H3) LEN(E3),LEN(H3)))
gt; gt;
gt; gt; My problem is when the street name is two words (North Main Street). I
gt; gt; get
gt; gt; a street name of North and a street type of Main Street. It is also
gt; gt; possible
gt; gt; that the street may have a 3 word name.
gt; gt;
gt; gt; My first qusetion is how can I do a search starting from the right (RIGHT
gt; gt; function does not do it) to find the last word in the string?
gt; gt;
gt; gt; Also, is there a cleaner way to parse the strings than I have done?
gt; gt;
gt; gt; Thanks for you help.
gt; gt;
gt; gt;
gt;
gt;