Text Function Formulas - Find/Substitute/Replace

[CleverUserName]

Hello,
I have Street Names in Column A. They are all different and may contain apartment or unit numbers at the end.

I would like to search the text in the cell from RIGHT to LEFT to find the first instance of "ST" or "AVE" or "RD". I would then like to remove any text to the right from that point in the cell leaving everything to left including my search string. I would also like to account for error handling in case the search string is not found.

Thank you.

Street Name	Desired Result
STANLEY ST.UNIT 2A	STANLEY ST
ALDINE ST 3B	ALDINE ST
S 18TH ST	S 18TH ST
AVELINE AVE APT N-3	AVELINE AVE
SMITH RD. 24G	SMITH RD

<tbody>
</tbody>

 

[XOR LX]

Hi,

It looks like you can also search from left to right if we include a space, i.e. search for " ST", " AVE" and " RD".

=LEFT(A2,LOOKUP(2^15,SEARCH(" "&{"AVE","RD","ST"},A2)+{3,2,2}))

Regards

 

[XOR LX]

Apologies - forgot you wanted error-handling. What do you want it to return in such cases?

This version will return the original string if none of "AVE", "RD", "ST" are found:

=LEFT(A2,LOOKUP(2^15,SEARCH({""," AVE"," RD"," ST"},A2)+{1000,3,2,2}))

Regards