Please help with splitting column???

  • Thread starter Thread starter TotallyConfused
  • Start date Start date
T

TotallyConfused

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.
 
You could try Data>Text 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
 
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.
 
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?
 
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.
Click to expand...

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.
 
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)
 
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"?
 
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("Bldg",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.
 
Thank you all very much for all your help.

Ron Coderre said:
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)
Click to expand...
 
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)
 
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.
 
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
 
Back
Top