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 need some way of identifying the position of the split. A common
character or type of characters such as spaces, numbers or specified number
of characters. Can you post a sample of 5 to 10 of the addresses. Rename the
Streets in your post to preserve privacy issues.

--
Regards,

OssieMac


"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 could try DataText to Columns and try to figure out what de-limiter to use.

Maybe a space or a comma?

If nothing logical, you may have to resort to a formula.


Gord Dibben MS Excel MVP

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.

Where I want to split is right before "Suite ##". However I do not want to
check the delimiter "space" because then the whole address would be split
into many columns. I just need to split before "Suite ##" I do not want to
use the text to columns that would create a lot of clean up after. Hope you
can help.

"OssieMac" wrote:

You need some way of identifying the position of the split. A common
character or type of characters such as spaces, numbers or specified number
of characters. Can you post a sample of 5 to 10 of the addresses. Rename the
Streets in your post to preserve privacy issues.

--
Regards,

OssieMac


"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.

I do not want to use the delimiter because I do not want to split and then
try to put all the columns back together. I need to split before the
"Suite##" which is a space but then it would split the whole address. An
address could have many spaces. I do not want to have to split and then put
together the columns again. There must be a better way to do this?

"Gord Dibben" wrote:

You could try DataText to Columns and try to figure out what de-limiter to use.

Maybe a space or a comma?

If nothing logical, you may have to resort to a formula.


Gord Dibben MS Excel MVP

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.

Post some explicit examples of your data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"TotallyConfused" wrote in
message ...
I do not want to use the delimiter because I do not want to split and then
try to put all the columns back together. I need to split before the
"Suite##" which is a space but then it would split the whole address. An
address could have many spaces. I do not want to have to split and then
put
together the columns again. There must be a better way to do this?

"Gord Dibben" wrote:

You could try DataText to Columns and try to figure out what de-limiter
to use.

Maybe a space or a comma?

If nothing logical, you may have to resort to a formula.


Gord Dibben MS Excel MVP

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.

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

Sorry, I should have put some sample :

Address field
126 Birmingham Dr., Suite 200
1936 Rodeo Dr., Suite 100
500 La Mesa, Bldg 5
etc

I need to display like this in two columns:

Address 1 Address 2
126 Birmingham Dr Suite 200
1936 Rodeo Dr Suite 100
500 La Mesa Bldg 5


Thank you.


"Ragdyer" wrote:

Post some explicit examples of your data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"TotallyConfused" wrote in
message ...
I do not want to use the delimiter because I do not want to split and then
try to put all the columns back together. I need to split before the
"Suite##" which is a space but then it would split the whole address. An
address could have many spaces. I do not want to have to split and then
put
together the columns again. There must be a better way to do this?

"Gord Dibben" wrote:

You could try DataText to Columns and try to figure out what de-limiter
to use.

Maybe a space or a comma?

If nothing logical, you may have to resort to a formula.


Gord Dibben MS Excel MVP

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.

With A1 containing some kind of address ending with (or without) a bldg or
suite reference.

Try this:

This formula (in parts for readability) returns the bldg or suite reference:
C1: =CHOOSE(SUM(COUNTIF(A1,"*"&{"bldg","suite"}&"*")*{ 1,2})+1,"n/a",

This formula returns the address:
MID(A1,SEARCH("bldg",A1),255),MID(A1,SEARCH("suite ",A1),255))

B1: =SUBSTITUTE(A1,C1,"")

Copy those formulas down as far as you need.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"TotallyConfused" wrote in
message ...
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.

How about using TTC (Text To Columns) and making the delimiter that comma
that appears to be in every line, just before the "Suite" and the "Bldg"?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"TotallyConfused" wrote in
message ...
Sorry, I should have put some sample :

Address field
126 Birmingham Dr., Suite 200
1936 Rodeo Dr., Suite 100
500 La Mesa, Bldg 5
etc

I need to display like this in two columns:

Address 1 Address 2
126 Birmingham Dr Suite 200
1936 Rodeo Dr Suite 100
500 La Mesa Bldg 5


Thank you.


"Ragdyer" wrote:

Post some explicit examples of your data.
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"TotallyConfused" wrote in
message ...
I do not want to use the delimiter because I do not want to split and
then
try to put all the columns back together. I need to split before the
"Suite##" which is a space but then it would split the whole address.
An
address could have many spaces. I do not want to have to split and
then
put
together the columns again. There must be a better way to do this?

"Gord Dibben" wrote:

You could try DataText to Columns and try to figure out what
de-limiter
to use.

Maybe a space or a comma?

If nothing logical, you may have to resort to a formula.


Gord Dibben MS Excel MVP

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.

Sorry I did not get back to you sooner. It looks like you have some answers
and might already have the problem sorted. However, here is my answer for
what it is worth:-

Assuming that the first address to be split is in cell A2, insert the
following formula in cell B2:-

=LEFT(A2,IF(ISERROR(SEARCH("Suite",A2,1)),SEARCH(" Bldg",A2,1),SEARCH("Suite",A2,1))-2)

Then insert the following formula in cell C2:-

=MID(A2,IF(ISERROR(SEARCH("Suite",A2,1)),SEARCH("B ldg",A2,1),SEARCH("Suite",A2,1)),255)

If the one of the strings does not appear in any address then you will get
#VALUE! error.


--
Regards,

OssieMac


"TotallyConfused" wrote:

Sorry, I should have put some sample :

Address field
126 Birmingham Dr., Suite 200
1936 Rodeo Dr., Suite 100
500 La Mesa, Bldg 5
etc

I need to display like this in two columns:

Address 1 Address 2
126 Birmingham Dr Suite 200
1936 Rodeo Dr Suite 100
500 La Mesa Bldg 5


Thank you.


"Ragdyer" wrote:

Post some explicit examples of your data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"TotallyConfused" wrote in
message ...
I do not want to use the delimiter because I do not want to split and then
try to put all the columns back together. I need to split before the
"Suite##" which is a space but then it would split the whole address. An
address could have many spaces. I do not want to have to split and then
put
together the columns again. There must be a better way to do this?

"Gord Dibben" wrote:

You could try DataText to Columns and try to figure out what de-limiter
to use.

Maybe a space or a comma?

If nothing logical, you may have to resort to a formula.


Gord Dibben MS Excel MVP

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.

Thank you all very much for all your help.

"Ron Coderre" wrote:

With A1 containing some kind of address ending with (or without) a bldg or
suite reference.

Try this:

This formula (in parts for readability) returns the bldg or suite reference:
C1: =CHOOSE(SUM(COUNTIF(A1,"*"&{"bldg","suite"}&"*")*{ 1,2})+1,"n/a",

This formula returns the address:
MID(A1,SEARCH("bldg",A1),255),MID(A1,SEARCH("suite ",A1),255))

B1: =SUBSTITUTE(A1,C1,"")

Copy those formulas down as far as you need.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"TotallyConfused" wrote in
message ...
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.

It might not matter much at this point, but it seems that I chopped up my
own post!

The formulas should have been:

This formula (in parts for readability) returns the bldg or suite reference:
C1: =CHOOSE(SUM(COUNTIF(A1,"*"&{"bldg","suite"}&"*")*{ 1,2})+1,"n/a",
MID(A1,SEARCH("bldg",A1),255),MID(A1,SEARCH("suite ",A1),255))

This formula returns the address:
B1: =SUBSTITUTE(A1,C1,"")
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Ron Coderre" wrote in message
...
With A1 containing some kind of address ending with (or without) a bldg or
suite reference.

Try this:

This formula (in parts for readability) returns the bldg or suite
reference:
C1: =CHOOSE(SUM(COUNTIF(A1,"*"&{"bldg","suite"}&"*")*{ 1,2})+1,"n/a",

This formula returns the address:
MID(A1,SEARCH("bldg",A1),255),MID(A1,SEARCH("suite ",A1),255))

B1: =SUBSTITUTE(A1,C1,"")

Copy those formulas down as far as you need.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"TotallyConfused" wrote in
message ...
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.

Good idea.

Wish I had thought of that<g


Gord

On Sun, 2 Mar 2008 18:38:19 -0800, "Ragdyer" wrote:

How about using TTC (Text To Columns) and making the delimiter that comma
that appears to be in every line, just before the "Suite" and the "Bldg"?

Sometimes OPs need a little accentuation Gord.

You see how (s)he just didn't seem to grasp the simplicity of your
intelligent guess ... one that you made without even knowing the actual data
configuration.<bg

You know ... can't see the forest 'cause the trees are in the way.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Good idea.

Wish I had thought of that<g


Gord

On Sun, 2 Mar 2008 18:38:19 -0800, "Ragdyer"
wrote:

How about using TTC (Text To Columns) and making the delimiter that comma
that appears to be in every line, just before the "Suite" and the "Bldg"?

Yeah, I know but my nose is still out of joint from the snowman I took on 17th
hole this morning.

PLUS........I don't get to see any funny characters<g


Gord

On Mon, 3 Mar 2008 14:59:21 -0800, "RagDyer" wrote:

Sometimes OPs need a little accentuation Gord.

You see how (s)he just didn't seem to grasp the simplicity of your
intelligent guess ... one that you made without even knowing the actual data
configuration.<bg

You know ... can't see the forest 'cause the trees are in the way.