Can text in one cell be split into several cells?

[Mistie]

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.

 

[Dave F]

You can use text to column and split them on the basis of the space between
the city and state.

 

[Sandy]

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

 

[Mistie]

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

 

[Mistie]

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

 

[Michael]

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

 

[Mistie]

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

 

[Ron Rosenfeld]

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

 

[Mistie]

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]

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