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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
130 Lagorce CirMiami Beach, FL 3314130 Lagorce CirMiami Beach
214 Tahiti Beach Island RdMiami, FL 3314314 Tahiti Beach Island RdMiami
3
33 Arvida PkwyMiami, FL 3315633 Arvida PkwyMiami
42900 N Bay RdMiami Beach, FL 331402900 N Bay RdMiami Beach
585 Arvida PkwyMiami, FL 3315685 Arvida PkwyMiami
61400 W 28th StMiami Beach, FL 331401400 W 28th StMiami Beach
716 Palm AveMiami Beach, FL 3313916 Palm AveMiami Beach
8400 Arvida PkwyCoral Gables, FL 33156400 Arvida PkwyCoral Gables
9
3551 Main HwyMiami, FL 331333551 Main HwyMiami
1024 S Hibiscus DrMiami Beach, FL 3313924 S Hibiscus DrMiami Beach
11268 Bal Bay DrBal Harbour, FL 33154268 Bal Bay DrBal Harbour
121400 W 28th StMiami Beach, FL 331401400 W 28th StMiami Beach
13599 Ocean BlvdGolden Beach, FL 33160599 Ocean BlvdGolden Beach
14135 Ocean BlvdGolden Beach, FL 33160135 Ocean BlvdGolden Beach
15
2 Indian Creek Island RdIndian Creek Village, FL 331542 Indian Creek Island RdIndian Creek Village
161401 W 27th StMiami Beach, FL 331401401 W 27th StMiami Beach
1764 Lagorce CirMiami Beach, FL 3314164 Lagorce CirMiami Beach
184420 N Bay RdMiami Beach, FL 331404420 N Bay RdMiami Beach
195700 N Bay RdMiami Beach, FL 331405700 N Bay RdMiami Beach
20
40 Indian Creek Island RdIndian Creek Village, FL 3315440 Indian Creek Island RdIndian Creek Village
2112 Tahiti Beach Island RdMiami, FL 3314312 Tahiti Beach Island RdMiami
227 Harbor PtKey Biscayne, FL 331497 Harbor PtKey Biscayne
23271 N Hibiscus DrMiami Beach, FL 33139271 N Hibiscus DrMiami Beach
24555 Ocean BlvdGolden Beach, FL 33160555 Ocean BlvdGolden Beach
2513 Tahiti Beach Island RdCoral Gables, FL 3314313 Tahiti Beach Island RdCoral Gables
26
94 Lagorce CirMiami Beach, FL 3314194 Lagorce CirMiami Beach
27130 Casuarina ConcourseCoral Gables, FL 33143130 Casuarina ConcourseCoral Gables
28181 E Sunrise AveCoral Gables, FL 33133181 E Sunrise AveCoral Gables
2921 Tahiti Beach Island RdCoral Gables, FL 3314321 Tahiti Beach Island RdCoral Gables
30510 S Mashta DrKey Biscayne, FL 33149510 S Mashta DrKey Biscayne
31
4900 Lake LnCoral Gables, FL 331564900 Lake LnCoral Gables
32501 Ocean BlvdGolden Beach, FL 33160501 Ocean BlvdGolden Beach
334949 Pine Tree DrMiami Beach, FL 331404949 Pine Tree DrMiami Beach
34221 Casuarina ConcourseCoral Gables, FL 33143221 Casuarina ConcourseCoral Gables
35130 Casuarina ConcCoral Gables, FL 33143130 Casuarina ConcCoral Gables
36830 Harbor DrKey Biscayne, FL 33149830 Harbor DrKey Biscayne
37
6923 Valencia DrMiami Beach, FL 331096923 Valencia DrMiami Beach
383040 N Bay RdMiami Beach, FL 331403040 N Bay RdMiami Beach
3936 Indian Creek Island RdIndian Creek Village, FL 3315436 Indian Creek Island RdIndian Creek Village
40145 Ocean BlvdGolden Beach, FL 33160145 Ocean BlvdGolden Beach
41205 E San Marino DrMiami Beach, FL 33139205 E San Marino DrMiami Beach
42
4 Palm AveMiami Beach, FL 331394 Palm AveMiami Beach
435396 N Bay RdMiami Beach, FL 331405396 N Bay RdMiami Beach
441025 Mariner DrKey Biscayne, FL 331491025 Mariner DrKey Biscayne
4555 Solano PradoCoral Gables, FL 3315655 Solano PradoCoral Gables
46229 Ocean BlvdGolden Beach, FL 33160229 Ocean BlvdGolden Beach
476919 Sunrise TeCoral Gables, FL 331336919 Sunrise TeCoral Gables
48
1410 W 25th StMiami Beach, FL 331401410 W 25th StMiami Beach
491525 N View DrMiami Beach, FL 331401525 N View DrMiami Beach
509335 Balada StCoral Gables, FL 331569335 Balada StCoral Gables

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
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
27Sold for $11,500,000 on Mar 3, 2008
287 Bed 6 Full,2 Half Ba 6,345 Sq Ft 0.81 Acre Lot
29Single Family Home
30400 Arvida PkwyCoral Gables, FL 33156
31Sold for $11,500,000 on Mar 29, 2013
328 Bed 9 Full,4 Half Ba 13,849 Sq Ft 0.95 Acre Lot
33Single Family Home
343551 Main HwyMiami, FL 33133
35Sold for $11,500,000 on Oct 21, 2008
364 Bed, 8,184 Sq Ft 6.91 Acre Lot
37Single Family Home
3824 S Hibiscus DrMiami Beach, FL 33139
39Sold for $10,600,000 on May 1, 2008
405 Bed, 13,000 Sq Ft 0.45 Acre Lot
41Single Family Home
42268 Bal Bay DrBal Harbour, FL 33154
43Sold for $10,350,000 on May 4, 2007
447 Bed, 8 Bath 9,772 Sq Ft 0.49 Acre Lot
45Single Family Home
461400 W 28th StMiami Beach, FL 33140
47Sold for $10,200,000 on May 28, 2004
487 Bed 7 Full,2 Half Ba 9,933 Sq Ft 0.46 Acre Lot
49Single Family Home
50599 Ocean BlvdGolden Beach, FL 33160
51Sold for $10,200,000 on Jun 2, 2008
526 Bed 6 Full,1 Half Ba 7,456 Sq Ft 0.47 Acre Lot
53Single Family Home
54135 Ocean BlvdGolden Beach, FL 33160
55Sold for $10,150,000 on Apr 13, 2006
566 Bed 6 Full,2 Half Ba 15,692 Sq Ft 0.49 Acre Lot
57Single Family Home
582 Indian Creek Island RdIndian Creek Village, FL 33154
59Sold for $10,100,000 on Apr 25, 2006
609 Bed, 15,000 Sq Ft 1.84 Acre Lot
61Single Family Home
621401 W 27th StMiami Beach, FL 33140
63Sold for $10,000,000 on Jan 25, 2008
648 Bed, 9,626 Sq Ft 0.46 Acre Lot
65Single Family Home
6664 Lagorce CirMiami Beach, FL 33141
67Sold for $9,700,000 on Jun 30, 2006
686 Bed 7 Full,1 Half Ba 10,296 Sq Ft 0.68 Acre Lot
69Single Family Home
704420 N Bay RdMiami Beach, FL 33140
71Sold for $9,575,000 on Dec 22, 2005
725 Bed, 8,369 Sq Ft 0.39 Acre Lot
73Single Family Home
745700 N Bay RdMiami Beach, FL 33140
75Sold for $9,330,000 on Nov 20, 2006
764 Bed, 8,333 Sq Ft 1.03 Acre Lot
77Single Family Home
7840 Indian Creek Island RdIndian Creek Village, FL 33154
79Sold for $9,300,000 on Mar 25, 2010
807 Bed 8 Full,2 Half Ba 7,888 Sq Ft 1.23 Acre Lot
81Single Family Home
8212 Tahiti Beach Island RdMiami, FL 33143
83Sold for $9,200,000 on May 21, 2008
845 Bed, 10,441 Sq Ft 0.93 Acre Lot
85Single Family Home
867 Harbor PtKey Biscayne, FL 33149
87Sold for $9,000,000 on May 3, 2006
883 Bed, 2 Bath 2,522 Sq Ft 0.48 Acre Lot
89Single Family Home

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</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

I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
80
ipbr21054
I
SaraWitch
  • Solved
If cell matches data in a range of other cells show match
2
Replies
14
Views
602
awoohaw
awoohaw
B
  • Question
VBA/Formula to only run lookup if a cells value is a match
Replies
0
Views
336
Benjo83
B
B
  • Solved
Issue rearranging/sorting data based on multiple columns
Replies
1
Views
232
BasicContract8989
B
W
  • Question
VBA Help - Lookup Match Between 2 Worksheets
Replies
0
Views
486
Web_Jim
W

Forum statistics

Threads
1,214,646
Messages
6,120,715
Members
448,985
Latest member
chocbudda

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