Splitting Merged Data

[kennyfromcs]

Hi I have a file of about 7k rows where for some reason the Street and City got merged together, and i'm trying to find a way to split them up so i can have Street address and city on 2 separate columns

Here are some examples (so you can see how they differ one from another and reason i cannot use text to columns)

106 1/2 N Harbor BlvdFullerton
607 S Hill St Ste 304 (3rd Floor)Los Angeles
10343 E County Highway 30A Unit 111Panama City Beach
364 E Palmetto Park Rd Ste 3Boca Raton
8222 13th AveBrooklyn
711 3rd St S Ste 2Jacksonville Beach

I would appreciate any help someone can propose, I can't do it manually on 7k rows


Thank you in advance

[FlameRetired]

Withdrawn by FR.

[FDibbins]

Hi, welcome to the forum

wow that data sure is a mess Where is it coming from?

[FlameRetired]

Without the outrageous good fortune of a lookup table of valid city names I would have no idea how to do this. With such a lookup table in H1:H6 this formula in B1 and copied down and across column C would work.

=TRIM(MID(REPLACE($A1,LOOKUP(10^10,FIND($H$1:$H$6,$A1)),0,REPT(" ",256)),(COLUMNS($A:A)-1)*256+1,256))

I've attached a sample file of what I'm talking about.

BTW: Some second thoughts. If one of your cities is not on the lookup list but is a partial match you could get some incorrect results with this approach.

[kennyfromcs]

Wow tyvm for the clear example and explanations FR
If I can find a list it will be very useful.

Quick question on an idea I just had, one of the common element in all those addresses is the fact that there is always a lowercase or number followed by an Upper case.
Is it possible to insert a comma in between those 2 characters via formula every time one of those 2 possibility happens?
Or worse case even conditionally Highlight the word containing it?

Let me know and thank you very much for your help

UPDATE #2
I did find a pretty good list of all US cities, and your formulas worked, the only issue I have is many street names are also city names, or cities like York , would split it New York...
For a worse case solution it's great , but let me know if you think idea on top is doable pls

[FlameRetired]

Thanks for the feedback.

Glad you found a list. Yes on the duplicates. I never thought about streets having the same names as cities.....just partial matches on cities.

The idea you ask about is a good one.....if I only knew how to implement it. I don't. I looked for other patterns but found none.

In another vein and as an afterthought, there is a simpler pair of formulas I thought of to replace the one I uploaded.

In C1 this formula copied down column C

=LOOKUP(2,1/FIND($H$1:$H$6,$A1),$H$1:$H$6)

Then in B1 and copied down column B

=SUBSTITUTE($A1,$C1,"")

Both much easier on the eyes.

[FlameRetired]

.........The idea you ask about is a good one.....if I only knew how to implement it. I don't. I looked for other patterns but found none...........

I have now. The reworked WB is attached. I had to build a reference range using your idea.......it works. The reference range (26 columns x 38 rows) includes the characters preceding the first letter of city names. As you add to that update the reference range (it's a concatenating formula) and the parsing formula.

The formula array-entered into C1 and filled across and down column D is

=TRIM(MID(REPLACE($A1,MAX(IFERROR(FIND($I$2:$AH$39,$A1),0))+1,0,REPT(" ",256)),(COLUMNS($A:A)-1)*256+1,256))

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

I duplicated your sample data enough times to make more than 7K rows. It took about 4 seconds to calculate. If you need to update the lookup reference range that formula took my machine about 10 seconds to calculate.

I was pleasantly surprised to find that this works and much better than the cities lookup approach.

[kennyfromcs]

Yes this Array works amazingly well

You guys rock! The community in this forum seems amazing. 2 post I made so far and on both I got such great help, that I'm actually starting to gain back faith in humanity :D

I hope to be able to give back as well very soon

[FlameRetired]

Glad it helped. Thanks for the feedback.