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.


[TABLE="width: 370"]
<tbody>[TR]
[TD]Street Name[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD]STANLEY ST.UNIT 2A[/TD]
[TD]STANLEY ST[/TD]
[/TR]
[TR]
[TD]ALDINE ST 3B[/TD]
[TD]ALDINE ST[/TD]
[/TR]
[TR]
[TD]S 18TH ST[/TD]
[TD]S 18TH ST[/TD]
[/TR]
[TR]
[TD]AVELINE AVE APT N-3[/TD]
[TD]AVELINE AVE[/TD]
[/TR]
[TR]
[TD]SMITH RD. 24G[/TD]
[TD]SMITH RD[/TD]
[/TR]
</tbody>[/TABLE]

 

[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