Can text in one cell be split into several cells?
 

I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Can text in one cell be split into several cells?
 

You can use text to column and split them on the basis of the space between
the city and state.
--
Brevity is the soul of wit.


"Mistie" wrote:

I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Can text in one cell be split into several cells?
 

Have you tried using text to columns with the other check box selected
and using a space as the delimeter?


Mistie wrote:
I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Can text in one cell be split into several cells?
 

Well, actually, I tried that. However, there are some cities that have two
or three names, and they mix with the states that only have one. So, that
doesn't work.

Thank you though.

Mistie

"Dave F" wrote:

You can use text to column and split them on the basis of the space between
the city and state.
--
Brevity is the soul of wit.


"Mistie" wrote:

I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Can text in one cell be split into several cells?
 

I tried to do that since there seems to be 3 spaces between the city and
state. But, it did not take 3 spaces (or any spaces) in the other box.

Thank you for trying.

Mistie

"Sandy" wrote:

Have you tried using text to columns with the other check box selected
and using a space as the delimeter?


Mistie wrote:
I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Can text in one cell be split into several cells?
 

Mistie, is the State a name or a 2 letter code at the end of the text string?
--
Sincerely, Michael Colvin


"Mistie" wrote:

I tried to do that since there seems to be 3 spaces between the city and
state. But, it did not take 3 spaces (or any spaces) in the other box.

Thank you for trying.

Mistie

"Sandy" wrote:

Have you tried using text to columns with the other check box selected
and using a space as the delimeter?


Mistie wrote:
I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Can text in one cell be split into several cells?
 

It is a letter abbreviation at the end of the text of that cell. It would
not be such a problem except that the cities are different word lengths, so
they do not split correctly. I can write a function to extract the states
into their own column, but then I still have it listed in the other column
still. I am out of ideas. Please help if you can.

Thank you,
Mistie

"Michael" wrote:

Mistie, is the State a name or a 2 letter code at the end of the text string?
--
Sincerely, Michael Colvin


"Mistie" wrote:

I tried to do that since there seems to be 3 spaces between the city and
state. But, it did not take 3 spaces (or any spaces) in the other box.

Thank you for trying.

Mistie

"Sandy" wrote:

Have you tried using text to columns with the other check box selected
and using a space as the delimeter?


Mistie wrote:
I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Can text in one cell be split into several cells?
 

On Fri, 8 Sep 2006 13:31:01 -0700, Mistie <Mistie @discussions.microsoft.com
wrote:

I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Assuming the state is represented by a two letter abbreviation, and your data
is in A1, then

City:

=LEFT(A1,-1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

State:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),2)


--ron

Can text in one cell be split into several cells?
 

Oh, I knew there had to be a formula for this....I just wasn't smart enough
to figure it out. I put it in and it worked perfectly. Thank you, thank
you, thank you!!

Mistie

"Ron Rosenfeld" wrote:

On Fri, 8 Sep 2006 13:31:01 -0700, Mistie <Mistie @discussions.microsoft.com
wrote:

I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a
function or way to split them up. There are no commas in between and they
are not lined up to be able to use text to column. I need help, please?

Thank you.

Assuming the state is represented by a two letter abbreviation, and your data
is in A1, then

City:

=LEFT(A1,-1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

State:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),2)


--ron

Can text in one cell be split into several cells?
 

On Sat, 9 Sep 2006 19:09:01 -0700, Mistie
wrote:

Oh, I knew there had to be a formula for this....I just wasn't smart enough
to figure it out. I put it in and it worked perfectly. Thank you, thank
you, thank you!!

Mistie


You're welcome. Glad to help. Thanks for the feedback.

--ron