Separating City Name From Addresses Without Delimiter

**probuddha** · 10-10-2017, 04:06 PM

Hi

Excel 2013

I have a list of 4422 addresses where the city name is suffixed at the end of the address.

The problem is that there is no delimiter in front of the city names and the number of words in the City Name varies between 1-3

How could I split them up so that the city is in a new column.

Please find a sample list attached which shows how I have it and how I want it

Kindly advise

Thanks

**mehmetcik** · 10-10-2017, 04:31 PM

Formula for B3.

Formula:

Please Login or Register  to view this content.

Formula for C3 entered using Ctrl Shift Enter

Formula:

Please Login or Register  to view this content.

Then fill down.

**sandy666** · 10-10-2017, 05:33 PM

Just for fun with PowerQuery

**mjr veverka** · 10-10-2017, 06:02 PM

With vba

**probuddha** · 10-11-2017, 12:21 PM

Thanks a lot, guys

mehmetcik, your formula worked just fine.

Thanks again

**sandy666** · 10-11-2017, 01:23 PM

Thanks for the feedback & rep

Separating City Name From Addresses Without Delimiter

LinkBack

Thread Tools

Rate This Thread

Display

Separating City Name From Addresses Without Delimiter

Re: Separating City Name From Addresses Without Delimiter

Re: Separating City Name From Addresses Without Delimiter

Re: Separating City Name From Addresses Without Delimiter

Re: Separating City Name From Addresses Without Delimiter

Re: Separating City Name From Addresses Without Delimiter

Thread Information

Users Browsing this Thread

Similar Threads

Separating alpha and numeric characters, no delimiter, varying lengths

[SOLVED] Separating alpha and numeric characters, no delimiter, varying lengths

Separating Street Address City State Zip that only contain spaces

[SOLVED] Separating multiple data (w delimiter) from one cell to many cells

Separating Name and Address Phone Number with Odd Delimiter

Help splitting addresses into separate columns for City, State and Zip

Separating Name and Address with No Delimiter

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions