how to find the last instance of a character in a cell

[Im_Offset]

see the attached spreadsheet....
Column B is a list of complete addresses (number, street name, and street type). The
street type" is basically anything to the right of the last " " (space) in the address (i.e. Drive, or Road, or LN). I want to separate those three things into columns C, D, and E respectively. I have already put the number in column C using "Left" and "Find" functions. How can I get all characters to the right of the last space (" ") into column E, and then get the word(s) between the number and the ending into column D?

Thank you in advance for all input you can give me.

I am learning, but still,
Im_Offset

 

[AlanSidman]

Using Power Query/Get and Transform which is available in Excel versions 2010 and later. The Mcode is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Address", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Address", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Address.1", "Address.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Address.1", type text}, {"Address.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Address.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Address.1.1", "Address.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Address.1.1", Int64.Type}, {"Address.1.2", type text}})
in
    #"Changed Type2"

 

[Im_Offset]

Using Power Query/Get and Transform which is available in Excel versions 2010 and later. The Mcode is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Address", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Address", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Address.1", "Address.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Address.1", type text}, {"Address.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Address.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Address.1.1", "Address.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Address.1.1", Int64.Type}, {"Address.1.2", type text}})
in
    #"Changed Type2"

Thanks. But is there a way to do this using basic functions that I can type and copy into each cell?

 

[bosco_yip]

C2: =LEFT(B2,FIND(" ",B2)-1)

E2 : =TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",50)),50))

D2 : =TRIM(SUBSTITUTE(SUBSTITUTE(B2,C2,""),E2,""))

All copied down

Regards
Bosco

 

[Im_Offset]

Bosco!!! You are awesome. Thank you!!