On Sun, 2 Mar 2008 14:08:00 -0800, TotallyConfused
wrote:

I hope someone can help me with this. I have an address column. However,
this column has the main address and Suite ## or Bldg #. I need to separate
the main address and suite ## or bldge # so that the suite ## or Bldg ## is
in a separate column. How can I accomplish this as easy as possible? Thank
you in advance for any help you can provide.

You don't give much information, but perhaps the following will get you
started.

With your "main address" and Suite# or Bldg # in A1, assuming that they are
preceded by the word Suite of Bldg

The "main address", (or a blank if the cell is empty)

B1:

=IF(A1="","",IF(OR(ISNUMBER(SEARCH({"suite","bldg" },A1))),
TRIM(LEFT(A1,LOOKUP(TRUE,ISNUMBER(SEARCH({"suite", "bldg"},A1)),
SEARCH({"suite","bldg"},A1))-1)),A1))

Suite (or Bldg) and all to the right of that:

C1:

=IF(OR(ISNUMBER(SEARCH({"suite","Bldg"},A1))),MID( A1,LOOKUP(TRUE,ISNUMBER(
SEARCH({"suite","Bldg"},A1)),SEARCH({"suite","Bldg "},A1)),255),"")




--ron