reverse concatenation

[christy_nf]

I have a column containing street names, like Water Street and Ocean Pond Road. I need to separate the street type (i.e. Street, Road) into its own column and leave the street name itself in one column. I tried using the "Text to Column" command using a space as the delimiter, but the problem is that some street names have spaces in them and it splits them up. For example, I would get "Ocean" in one column, "Pond" in another column, and "Road" in another column. I do want the "Road" in its own column but I want "Ocean Pond" all in one column. Understand?

Does anyone have any ideas??? Thanks!

 

[Bruno_x]

You could thy this :
I assume that the street names are in Column A, starting at row 3

-Put the code in B3 and down until the last line
- copy column B en paste the values

=if(Right(A3;4)="Road"; Left(A3;Len(A3)-5);
if(Right(A3;6)="Street"; Left(A3;Len(A3)-7);""))

 

[tusharm]

Search Google (http://www.google.com/advanced_group_search?hl=en) for 'find last space' (w/o the quotes) in the newsgroups microsoft.public.excel.* and you will find an incredible number of solutions, including this array-formula from Harlan Grove (http://groups.google.com/groups?as_umsgid=06889872.786920ff@usw-ex0109-069.remarq.com):<pre>=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1),0)-1)</pre>