Separate Suite # from addresses

Hello

Request you guys to kindly offer me a solution to help separate Suite #s from addresses

All the suite #s are at the end of addresses and always starts with 'ste', like:-

13859 Progress Blvd Ste 300
5072 Annunciation Cir Ste 326
17971 Biscayne Blvd Ste 214
2999 NE 191st St Ste 900
18205 Biscayne Blvd Ste 2201
18851 NE 29th Ave Ste 1005

How can I strip them off the addresses and put them in a separate column. Kindly note that anything apart from the 'Ste #' should not be stripped.

So addresses like

21195 Helmsman Dr Apt F12
19101 Mystic Pointe Dr # 2108

..etc should not be stripped


Many thanks in advance

Thanks a lot, Glenn. That works fine.

However, I also want to remove the Ste # from the main address when it is being split into a new column.

So for example, for an address like:-

13859 Progress Blvd Ste 300

I want the 'Ste 300' part to be moved to a new column and the same removed from the main address as well. I need this so that I can sort the addresses without the Ste #s

Thanks Glenn, I should have made it clear at my original post.

I applied your revised formula, but it didn't quite work out properly.

Could you please have a look at the attached spreadsheet and advise

Thanks

I see, so I need to make two columns, one for (revised) addresses and another for Suite #s and run the following formulas

For addresses without suite #

Please Login or Register  to view this content.

For just Ste #s

Please Login or Register  to view this content.

This can work for me.

I was of the idea that I would need to make just one column for the Ste #s and the formula would strip off the Ste #s from the main addresses and also place them in the new Ste column simultaneously. So a single formula would do both.

But I guess that won't be possible?

Thanks

Hi Glenn

One quick question:-

In addition to separating the Ste #s from the addresses, if I also want to separate the Apt #, Fl#, Rm# , then what should be the formulas like?


Thanks