Merging text in cells

  • Thread starter Thread starter TonyK
  • Start date Start date
T

TonyK

I have a text file and I have imported it into excel and
I am having problem with seperating cities and states,
since they are not evenly spaced so I can't import
fixwidth, so I used delimited and it seperates the the
cities with more than two words.

What I would like to do is just merge the columns of the
cities and be done but I can's seem to find the answer.

Any suggestions would be helpful.

Thanks in advance.

Tony
 
Assuming the cities are now in B and state in C, or city in B and C, state in
D, in E, to get the city:

=IF(COUNTA(B1:D1)=3,B1&C1,B1)

in F, to get the state

=IF(ISBLANK(D1),C1,D1)

Copy down as far as needed. Then convert these formulas to their values by
Edit/Copy, then Edit/Paste Special and select the Values option. Once you've
done that, you can delete columns B:D.

Modify the formulas to suit you layout.
 
Worked great, how would i put a space in between the
words. I had to use space as the seperator because I
wanted to seperate the city from state but now I need to
combine the city back together with spaces.

Thanks for the help
Tony
 
Tony

To insert spaces use(example only)

=A1 & " " & B1 & " " & C1

Gord Dibben Excel MVP
 
Back
Top