Split data into separate columns

if you don't want to use a formula then just use the text to columns feature using space as the delimiter.

Alternatively, the Text to columns command (delimited with space as delimiter) should accomplish this as well.

If Google sheets is an allowable alternative, I think this is a good opportunity to use their SPLIT() function (not yet available in Excel, unless you write your own UDF based on the VBA Split() function).

Glad it worked for you and thanks for the rep!

Although suggested formula will work fine with town names with one word it will fail if the town names have two or three words.
Use this formula
Enter in B1 and copy across and down

Formula:

Please Login or Register  to view this content.

v	A	B	C	D
1	Bessemer AL 35022	Bessemer	AL	35022
2	Mobile IA 56608	Mobile	IA	56608
3	Leoncavallo CA 82115	Leoncavallo	CA	82115
4	Tanner MO 46801	Tanner	MO	46801
5	Huntsville OK 75805	Huntsville	OK	75805
6	Plant City FL 33565	Plant City	FL	33565
7	Saint Petersburg FL 33701	Saint Petersburg	FL	33701

Here's another formula option.

Assumes the zip codes will always be the 5 digit codes.

Data Range

	A	B	C	D
15	Pittsburgh PA 15225	Pittsburgh	PA	15225
16	Beaver Falls PA 15010	Beaver Falls	PA	15010
17	Rochester PA 15074	Rochester	PA	15074
18	Fond Du Lac WI 54935	Fond Du Lac	WI	54935

This formula entered in B15:

=LEFT(A15,LEN(A15)-9)

This formula entered in C15:

=MID(A15,LEN(B15)+2,2)

This formula entered in D15:

=RIGHT(A15,5)

Select B15:D15 and copy down as needed.