Adding comma after city in a cell with City State Zip-CodePudding

I am having trouble adding a comma after the city in a column with City State Zip.

Example:

New Bern NC 27856

Wilson NC 27866

Desired Result:

New Bern, NC 27856

Wilson, NC 27866

I am struggling because some cities are more than one word, so I cant use a substitute function. Also, text to columns is a mess when trying to manipulate city, state zip.

Any suggestions?

THANK YOU!

CodePudding user response：

I just tried this and it worked fine:

=LINKS(B2;LENGTE(B2) - 7) & "," & RECHTS(B2;LENGTE(B2) - 7)

Obviously, you'll need to translate from Dutch to English commands:

LINKS() => LEFT()
LENGTE() => LENGTH()
RECHTS() => RIGHT()

The whole formula is based on the idea that you need the last 7 characters "XX YYYY" where "XX" is "NC" in your case, and "YYYY" is the postal code.