Help to split words with specific condition

Hi 'm trying to see if someone can help me find way to split any word in a cell that contains one of the following condition:

-a lowercase letter followed immediately by an uppercase letter, to insert a "," in between those 2 letters

-a Number Followed immediately by an uppercase letter, to insert a "," in between those 2 letters

Examples:


4125 Blackhawk Plaza Circle Suite 100Danville would become 4125 Blackhawk Plaza Circle Suite 100,Danville
572 Route 303Blauvelt would become 572 Route 303,Blauvelt
353 Bleecker StNew York would become 353 Bleecker St,New York


Any idea would be greatly appreciated , I have to do this on 7k rows

Thank you in advance,

Just a comment here. If this is headed to a mailing list, the Post Office does not want any commas in the address. Actually, no punctuation at all and they prefer the entire address in uppercase.

http://pe.usps.com/businessmail101/a...eryAddress.htm

Originally Posted by p24leclerc

try this macro:
[The macro is programmed to look at all cells in column A starting at cell A1. Change that to fit your data.

TYVM for your help, that works great, now I just to manually fix a couple hundred instead of thousands (some split at wrong place example: 48W 48th StreetNew York would split at 48 W)

But still a huge help

Thx again

tyvm again for your help p24, even less for me to do

And Btw I just paid attention to your Avatar.

I'm from Quebec too! Vive Le Quebec!

Originally Posted by Len Silva

Just a comment here. If this is headed to a mailing list, the Post Office does not want any commas in the address. Actually, no punctuation at all and they prefer the entire address in uppercase.

http://pe.usps.com/businessmail101/a...eryAddress.htm

Ty for the info Len, but I wanted comma to be able to do a simple text to column to have city in a separate column.