Issue with Database Clean Up. Split info into 2 cells but issue with data is 1string. Thanks!

B

BrianZ

New Member
Joined
Dec 30, 2013
Messages
6
Hello, this is my first post so please inform me if I am doing anything wrong or there something I can clarify.
I am working on a project and have ran into an problem with separating lines of data. So let me first show what I am doing because I wont want to take an unnecessary block of your time. I have lines of data which have address and city smash together
e.g. 33 Arvda PkwMiamI
2900 N Bay RdMiami Beach
85 Arida PkwyMai
1400 W 28h StMiami Beach

(I have attach my file which shows [I cant find why i cant attach so i will post below this post, sorry!] what I am trying to make the data look like. The column is name Address+ City (Column F) and is located in Sheet - Work In Progress Sheet , and my end goal is to make it look like column M & N )

Question/Problem:
My question is there way to match a column cells with a word bank like all of a state city and have it lead to two new columns with address and city?
The cities names are always at the end but Miami and Miami Beach can be confused. Does anyone have any ideas, thoughts, or advice to how to solve this problem?

Additional Info/Conditions:
There can be unlimited lines of data and eventually the plan is to do this with every state, creating a new workbook for each state. Also I have work with excel before but I only found out about macro and vba a week ago so I am trying to cover that hole of knowledge asap but I want to let you know ahead of time.

Lastly, thank you so much for reading my post, and any formulas, advices, ideas, and suggestions would be greatly appreciate. Thanks and have good day.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
http://i42.tinypic.com/2cmnsyw.png

2cmnsyw.png

2cmnsyw.png



htt://i42.tinypc.com/21eygdd.png
21eygdd.png
 
Last edited:
Upvote 0
BrianZ,

Welcome to the MrExcel forum.

You are posting pictures/graphics. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
I believe I have got thetml maker to work.
Excel 2007
GHI
30 Lagorce CirMiami Beach, FL 3314130 Lagorce CirMiami Beach
14 Tahiti Beach Island RdMiami, FL 3314314 Tahiti Beach Island RdMiami
33 Arvida PkwyMiami, FL 3315633 Arvida PkwyMiami
2900 N Bay RdMiami Beach, FL 331402900 N Bay RdMiami Beach
85 Arvida PkwyMiami, FL 3315685 Arvida PkwyMiami
1400 W 28th StMiami Beach, FL 331401400 W 28th StMiami Beach
16 Palm AveMiami Beach, FL 3313916 Palm AveMiami Beach
400 Arvida PkwyCoral Gables, FL 33156400 Arvida PkwyCoral Gables
3551 Main HwyMiami, FL 331333551 Main HwyMiami
24 S Hibiscus DrMiami Beach, FL 3313924 S Hibiscus DrMiami Beach
268 Bal Bay DrBal Harbour, FL 33154268 Bal Bay DrBal Harbour
1400 W 28th StMiami Beach, FL 331401400 W 28th StMiami Beach
599 Ocean BlvdGolden Beach, FL 33160599 Ocean BlvdGolden Beach
135 Ocean BlvdGolden Beach, FL 33160135 Ocean BlvdGolden Beach
2 Indian Creek Island RdIndian Creek Village, FL 331542 Indian Creek Island RdIndian Creek Village
1401 W 27th StMiami Beach, FL 331401401 W 27th StMiami Beach
64 Lagorce CirMiami Beach, FL 3314164 Lagorce CirMiami Beach
4420 N Bay RdMiami Beach, FL 331404420 N Bay RdMiami Beach
5700 N Bay RdMiami Beach, FL 331405700 N Bay RdMiami Beach
40 Indian Creek Island RdIndian Creek Village, FL 3315440 Indian Creek Island RdIndian Creek Village
12 Tahiti Beach Island RdMiami, FL 3314312 Tahiti Beach Island RdMiami
7 Harbor PtKey Biscayne, FL 331497 Harbor PtKey Biscayne
271 N Hibiscus DrMiami Beach, FL 33139271 N Hibiscus DrMiami Beach
555 Ocean BlvdGolden Beach, FL 33160555 Ocean BlvdGolden Beach
13 Tahiti Beach Island RdCoral Gables, FL 3314313 Tahiti Beach Island RdCoral Gables
94 Lagorce CirMiami Beach, FL 3314194 Lagorce CirMiami Beach
130 Casuarina ConcourseCoral Gables, FL 33143130 Casuarina ConcourseCoral Gables
181 E Sunrise AveCoral Gables, FL 33133181 E Sunrise AveCoral Gables
21 Tahiti Beach Island RdCoral Gables, FL 3314321 Tahiti Beach Island RdCoral Gables
510 S Mashta DrKey Biscayne, FL 33149510 S Mashta DrKey Biscayne
4900 Lake LnCoral Gables, FL 331564900 Lake LnCoral Gables
501 Ocean BlvdGolden Beach, FL 33160501 Ocean BlvdGolden Beach
4949 Pine Tree DrMiami Beach, FL 331404949 Pine Tree DrMiami Beach
221 Casuarina ConcourseCoral Gables, FL 33143221 Casuarina ConcourseCoral Gables
130 Casuarina ConcCoral Gables, FL 33143130 Casuarina ConcCoral Gables
830 Harbor DrKey Biscayne, FL 33149830 Harbor DrKey Biscayne
6923 Valencia DrMiami Beach, FL 331096923 Valencia DrMiami Beach
3040 N Bay RdMiami Beach, FL 331403040 N Bay RdMiami Beach
36 Indian Creek Island RdIndian Creek Village, FL 3315436 Indian Creek Island RdIndian Creek Village
145 Ocean BlvdGolden Beach, FL 33160145 Ocean BlvdGolden Beach
205 E San Marino DrMiami Beach, FL 33139205 E San Marino DrMiami Beach
4 Palm AveMiami Beach, FL 331394 Palm AveMiami Beach
5396 N Bay RdMiami Beach, FL 331405396 N Bay RdMiami Beach
1025 Mariner DrKey Biscayne, FL 331491025 Mariner DrKey Biscayne
55 Solano PradoCoral Gables, FL 3315655 Solano PradoCoral Gables
229 Ocean BlvdGolden Beach, FL 33160229 Ocean BlvdGolden Beach
6919 Sunrise TeCoral Gables, FL 331336919 Sunrise TeCoral Gables
1410 W 25th StMiami Beach, FL 331401410 W 25th StMiami Beach
1525 N View DrMiami Beach, FL 331401525 N View DrMiami Beach
9335 Balada StCoral Gables, FL 331569335 Balada StCoral Gables

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]

[TD="align: center"]20
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]

[TD="align: center"]26
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]

[TD="align: center"]31
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: right"][/TD]

[TD="align: center"]36[/TD]

[TD="align: right"][/TD]

[TD="align: center"]37
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"][/TD]

[TD="align: center"]40[/TD]

[TD="align: right"][/TD]

[TD="align: center"]41[/TD]

[TD="align: right"][/TD]

[TD="align: center"]42
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]43[/TD]

[TD="align: right"][/TD]

[TD="align: center"]44[/TD]

[TD="align: right"][/TD]

[TD="align: center"]45[/TD]

[TD="align: right"][/TD]

[TD="align: center"]46[/TD]

[TD="align: right"][/TD]

[TD="align: center"]47[/TD]

[TD="align: right"][/TD]

[TD="align: center"]48
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]49[/TD]

[TD="align: right"][/TD]

[TD="align: center"]50[/TD]

[TD="align: right"][/TD]

</tbody>

Column G is the data after taking only the address from the original set. Column H is what I am left with after using =left(G1, len(G1)-10)
Now for column I, I am trying to separate the address from the city, ex: Miami beach, coral gable, Miami, KeyBiscayne, etc.)
Anyone have any ideas to help solve this problem? Would a word bank be the most effective, maybe removing matching phrases or putting a comma before the matching ones. Any suggestion would be highly appreciate! Thanks and have a good day.
 
Upvote 0
BrianZ,

You latest screenshot is better.

But, you should give us a screenshot of column A's raw data.
 
Upvote 0
Sorry here is column A

Excel 2007
A
30 Lagorce CirMiami Beach, FL 33141
Sold for $17,000,000 on Oct 31, 2006
13 Bed, 11 Bath 13,447 Sq Ft 1.23 Acre Lot
Single Family Home
14 Tahiti Beach Island RdMiami, FL 33143
Sold for $14,245,000 on Apr 18, 2008
7 Bed, 14,495 Sq Ft 0.93 Acre Lot
Single Family Home
33 Arvida PkwyMiami, FL 33156
Sold for $12,750,000 on Nov 29, 2005
8 Bed, 13,086 Sq Ft
Single Family Home
2900 N Bay RdMiami Beach, FL 33140
Sold for $12,000,000 on May 1, 2006
12 Bed, 18,000 Sq Ft 0.94 Acre Lot
Single Family Home
85 Arvida PkwyMiami, FL 33156
Sold for $11,700,000 on Feb 26, 2008
8 Bed, 11,598 Sq Ft 0.83 Acre Lot
Single Family Home
1400 W 28th StMiami Beach, FL 33140
Sold for $11,600,000 on Sep 7, 2006
7 Bed, 9,933 Sq Ft 0.46 Acre Lot
Single Family Home
16 Palm AveMiami Beach, FL 33139
Sold for $11,500,000 on Mar 3, 2008
7 Bed 6 Full,2 Half Ba 6,345 Sq Ft 0.81 Acre Lot
Single Family Home
400 Arvida PkwyCoral Gables, FL 33156
Sold for $11,500,000 on Mar 29, 2013
8 Bed 9 Full,4 Half Ba 13,849 Sq Ft 0.95 Acre Lot
Single Family Home
3551 Main HwyMiami, FL 33133
Sold for $11,500,000 on Oct 21, 2008
4 Bed, 8,184 Sq Ft 6.91 Acre Lot
Single Family Home
24 S Hibiscus DrMiami Beach, FL 33139
Sold for $10,600,000 on May 1, 2008
5 Bed, 13,000 Sq Ft 0.45 Acre Lot
Single Family Home
268 Bal Bay DrBal Harbour, FL 33154
Sold for $10,350,000 on May 4, 2007
7 Bed, 8 Bath 9,772 Sq Ft 0.49 Acre Lot
Single Family Home
1400 W 28th StMiami Beach, FL 33140
Sold for $10,200,000 on May 28, 2004
7 Bed 7 Full,2 Half Ba 9,933 Sq Ft 0.46 Acre Lot
Single Family Home
599 Ocean BlvdGolden Beach, FL 33160
Sold for $10,200,000 on Jun 2, 2008
6 Bed 6 Full,1 Half Ba 7,456 Sq Ft 0.47 Acre Lot
Single Family Home
135 Ocean BlvdGolden Beach, FL 33160
Sold for $10,150,000 on Apr 13, 2006
6 Bed 6 Full,2 Half Ba 15,692 Sq Ft 0.49 Acre Lot
Single Family Home
2 Indian Creek Island RdIndian Creek Village, FL 33154
Sold for $10,100,000 on Apr 25, 2006
9 Bed, 15,000 Sq Ft 1.84 Acre Lot
Single Family Home
1401 W 27th StMiami Beach, FL 33140
Sold for $10,000,000 on Jan 25, 2008
8 Bed, 9,626 Sq Ft 0.46 Acre Lot
Single Family Home
64 Lagorce CirMiami Beach, FL 33141
Sold for $9,700,000 on Jun 30, 2006
6 Bed 7 Full,1 Half Ba 10,296 Sq Ft 0.68 Acre Lot
Single Family Home
4420 N Bay RdMiami Beach, FL 33140
Sold for $9,575,000 on Dec 22, 2005
5 Bed, 8,369 Sq Ft 0.39 Acre Lot
Single Family Home
5700 N Bay RdMiami Beach, FL 33140
Sold for $9,330,000 on Nov 20, 2006
4 Bed, 8,333 Sq Ft 1.03 Acre Lot
Single Family Home
40 Indian Creek Island RdIndian Creek Village, FL 33154
Sold for $9,300,000 on Mar 25, 2010
7 Bed 8 Full,2 Half Ba 7,888 Sq Ft 1.23 Acre Lot
Single Family Home
12 Tahiti Beach Island RdMiami, FL 33143
Sold for $9,200,000 on May 21, 2008
5 Bed, 10,441 Sq Ft 0.93 Acre Lot
Single Family Home
7 Harbor PtKey Biscayne, FL 33149
Sold for $9,000,000 on May 3, 2006
3 Bed, 2 Bath 2,522 Sq Ft 0.48 Acre Lot
Single Family Home

<colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]

[TD="align: center"]37[/TD]

[TD="align: center"]38[/TD]

[TD="align: center"]39[/TD]

[TD="align: center"]40[/TD]

[TD="align: center"]41[/TD]

[TD="align: center"]42[/TD]

[TD="align: center"]43[/TD]

[TD="align: center"]44[/TD]

[TD="align: center"]45[/TD]

[TD="align: center"]46[/TD]

[TD="align: center"]47[/TD]

[TD="align: center"]48[/TD]

[TD="align: center"]49[/TD]

[TD="align: center"]50[/TD]

[TD="align: center"]51[/TD]

[TD="align: center"]52[/TD]

[TD="align: center"]53[/TD]

[TD="align: center"]54[/TD]

[TD="align: center"]55[/TD]

[TD="align: center"]56[/TD]

[TD="align: center"]57[/TD]

[TD="align: center"]58[/TD]

[TD="align: center"]59[/TD]

[TD="align: center"]60[/TD]

[TD="align: center"]61[/TD]

[TD="align: center"]62[/TD]

[TD="align: center"]63[/TD]

[TD="align: center"]64[/TD]

[TD="align: center"]65[/TD]

[TD="align: center"]66[/TD]

[TD="align: center"]67[/TD]

[TD="align: center"]68[/TD]

[TD="align: center"]69[/TD]

[TD="align: center"]70[/TD]

[TD="align: center"]71[/TD]

[TD="align: center"]72[/TD]

[TD="align: center"]73[/TD]

[TD="align: center"]74[/TD]

[TD="align: center"]75[/TD]

[TD="align: center"]76[/TD]

[TD="align: center"]77[/TD]

[TD="align: center"]78[/TD]

[TD="align: center"]79[/TD]

[TD="align: center"]80[/TD]

[TD="align: center"]81[/TD]

[TD="align: center"]82[/TD]

[TD="align: center"]83[/TD]

[TD="align: center"]84[/TD]

[TD="align: center"]85[/TD]

[TD="align: center"]86[/TD]

[TD="align: center"]87[/TD]

[TD="align: center"]88[/TD]

[TD="align: center"]89[/TD]

</tbody>
Sheet2.1​



I am already using a code I found to separate the lines of data which is column G. The problem at hand is I have no idea to how to go from H to I
 
Last edited:
Upvote 0
BrianZ,

Sample raw data (not all 89 rows are shown for brevity):


Excel 2007
AB
1
230 Lagorce CirMiami Beach, FL 33141
3Sold for $17,000,000 on Oct 31, 2006
413 Bed, 11 Bath 13,447 Sq Ft 1.23 Acre Lot
5Single Family Home
614 Tahiti Beach Island RdMiami, FL 33143
7Sold for $14,245,000 on Apr 18, 2008
87 Bed, 14,495 Sq Ft 0.93 Acre Lot
9Single Family Home
1033 Arvida PkwyMiami, FL 33156
11Sold for $12,750,000 on Nov 29, 2005
128 Bed, 13,086 Sq Ft
13Single Family Home
142900 N Bay RdMiami Beach, FL 33140
15Sold for $12,000,000 on May 1, 2006
1612 Bed, 18,000 Sq Ft 0.94 Acre Lot
17Single Family Home
1885 Arvida PkwyMiami, FL 33156
19Sold for $11,700,000 on Feb 26, 2008
208 Bed, 11,598 Sq Ft 0.83 Acre Lot
21Single Family Home
221400 W 28th StMiami Beach, FL 33140
23Sold for $11,600,000 on Sep 7, 2006
247 Bed, 9,933 Sq Ft 0.46 Acre Lot
25Single Family Home
2616 Palm AveMiami Beach, FL 33139
Sheet1


After the macro we get these two colums (column B contains just the address's from column A):


Excel 2007
AB
1
230 Lagorce CirMiami Beach, FL 3314130 Lagorce CirMiami Beach, FL 33141
3Sold for $17,000,000 on Oct 31, 200614 Tahiti Beach Island RdMiami, FL 33143
413 Bed, 11 Bath 13,447 Sq Ft 1.23 Acre Lot33 Arvida PkwyMiami, FL 33156
5Single Family Home2900 N Bay RdMiami Beach, FL 33140
614 Tahiti Beach Island RdMiami, FL 3314385 Arvida PkwyMiami, FL 33156
7Sold for $14,245,000 on Apr 18, 20081400 W 28th StMiami Beach, FL 33140
87 Bed, 14,495 Sq Ft 0.93 Acre Lot16 Palm AveMiami Beach, FL 33139
9Single Family Home400 Arvida PkwyCoral Gables, FL 33156
1033 Arvida PkwyMiami, FL 331563551 Main HwyMiami, FL 33133
11Sold for $12,750,000 on Nov 29, 200524 S Hibiscus DrMiami Beach, FL 33139
128 Bed, 13,086 Sq Ft268 Bal Bay DrBal Harbour, FL 33154
13Single Family Home1400 W 28th StMiami Beach, FL 33140
142900 N Bay RdMiami Beach, FL 33140599 Ocean BlvdGolden Beach, FL 33160
15Sold for $12,000,000 on May 1, 2006135 Ocean BlvdGolden Beach, FL 33160
1612 Bed, 18,000 Sq Ft 0.94 Acre Lot2 Indian Creek Island RdIndian Creek Village, FL 33154
17Single Family Home1401 W 27th StMiami Beach, FL 33140
1885 Arvida PkwyMiami, FL 3315664 Lagorce CirMiami Beach, FL 33141
19Sold for $11,700,000 on Feb 26, 20084420 N Bay RdMiami Beach, FL 33140
208 Bed, 11,598 Sq Ft 0.83 Acre Lot5700 N Bay RdMiami Beach, FL 33140
21Single Family Home40 Indian Creek Island RdIndian Creek Village, FL 33154
221400 W 28th StMiami Beach, FL 3314012 Tahiti Beach Island RdMiami, FL 33143
23Sold for $11,600,000 on Sep 7, 20067 Harbor PtKey Biscayne, FL 33149
247 Bed, 9,933 Sq Ft 0.46 Acre Lot
25Single Family Home
2616 Palm AveMiami Beach, FL 33139
Sheet1


You can use the column B information to manually create/increase the road array:
road = Array("Ave", "Blvd", "Cir", "Dr", "Hwy", "Pkwy", "Pt", "Rd", "St")

And the last range of columns, columns D, E, F, and, G:


Excel 2007
BCDEFG
1AddressCityStateZip
230 Lagorce CirMiami Beach, FL 3314130 Lagorce CirMiami BeachFL33141
314 Tahiti Beach Island RdMiami, FL 3314314 Tahiti Beach Island RdMiamiFL33143
433 Arvida PkwyMiami, FL 3315633 Arvida PkwyMiamiFL33156
52900 N Bay RdMiami Beach, FL 331402900 N Bay RdMiami BeachFL33140
685 Arvida PkwyMiami, FL 3315685 Arvida PkwyMiamiFL33156
71400 W 28th StMiami Beach, FL 331401400 W 28th StMiami BeachFL33140
816 Palm AveMiami Beach, FL 3313916 Palm AveMiami BeachFL33139
9400 Arvida PkwyCoral Gables, FL 33156400 Arvida PkwyCoral GablesFL33156
103551 Main HwyMiami, FL 331333551 Main HwyMiamiFL33133
1124 S Hibiscus DrMiami Beach, FL 3313924 S Hibiscus DrMiami BeachFL33139
12268 Bal Bay DrBal Harbour, FL 33154268 Bal Bay DrBal HarbourFL33154
131400 W 28th StMiami Beach, FL 331401400 W 28th StMiami BeachFL33140
14599 Ocean BlvdGolden Beach, FL 33160599 Ocean BlvdGolden BeachFL33160
15135 Ocean BlvdGolden Beach, FL 33160135 Ocean BlvdGolden BeachFL33160
162 Indian Creek Island RdIndian Creek Village, FL 331542 Indian Creek Island RdIndian Creek VillageFL33154
171401 W 27th StMiami Beach, FL 331401401 W 27th StMiami BeachFL33140
1864 Lagorce CirMiami Beach, FL 3314164 Lagorce CirMiami BeachFL33141
194420 N Bay RdMiami Beach, FL 331404420 N Bay RdMiami BeachFL33140
205700 N Bay RdMiami Beach, FL 331405700 N Bay RdMiami BeachFL33140
2140 Indian Creek Island RdIndian Creek Village, FL 3315440 Indian Creek Island RdIndian Creek VillageFL33154
2212 Tahiti Beach Island RdMiami, FL 3314312 Tahiti Beach Island RdMiamiFL33143
237 Harbor PtKey Biscayne, FL 331497 Harbor PtKey BiscayneFL33149
24
25
26
Sheet1



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub ExtractAddress()
' hiker95, 01/06/2014
' http://www.mrexcel.com/forum/excel-questions/748164-issue-database-clean-up-split-info-into-2-cells-but-issue-data-1string-thanks.html
Dim a As Variant, b As Variant, road, s, s1, s2
Dim c As Range, nr As Long
Dim i As Long, ii As Long, rr As Long   ', ss As Long, ss1 As Long, ss2 As Long
Application.ScreenUpdating = False
Columns("B:G").ClearContents
road = Array("Ave", "Blvd", "Cir", "Dr", "Hwy", "Pkwy", "Pt", "Rd", "St")
a = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
  If InStr(a(i, 1), ", FL ") Then
    ii = ii + 1
    b(ii, 1) = a(i, 1)
  End If
Next i
Range("B2").Resize(UBound(b, 1)) = b
Columns(2).AutoFit
Cells(1, 4).Resize(, 4) = Array("Address", "City", "State", "Zip")
nr = 1
For Each c In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
  If InStr(c, ", FL ") Then
    For rr = LBound(road) To UBound(road)
      If InStr(c, road(rr)) Then
        nr = nr + 1
        s = Split(c, road(rr))
        Cells(nr, 4) = s(0) & road(rr)
        s1 = Split(s(1), ", ")
        Cells(nr, 5) = s1(0)
        s2 = Split(s1(1), " ")
        Cells(nr, 6) = s2(0)
        Cells(nr, 7) = s2(1)
        Exit For
      End If
    Next rr
  End If
Next c
Columns("B:G").AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractAddress macro.
 
Upvote 0
BrianZ,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Word to Excel - Not formatting correctly - Can someone help?
Replies
6
Views
267
rgillis32
R
M
  • Solved
Selective Match/Vlookup (?) on Various Criteria in Single Row
Replies
2
Views
256
m3ko
M
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
510
Peter_SSs
Peter_SSs
H
  • Question Question
Help - #unknown! error after adding pictures.
Replies
1
Views
721
myall_blues
myall_blues
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
883
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top