Hi Guys, I'm having a hard time with this issue! I have a data set that contains addresses with delimiters that are sometimes commas and other times spaces. I want to separate the data out to "street address", "city", "state", and "zip code". Sometimes the full address is listed, but other times the user only supplied partial information like the city and state - or just the state and zip. Here's a short example of what I'm working with. Any help you can provide on getting this information into the appropriate columns would be greatly appreciated!
[TABLE="width: 513"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2106 pacific ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]2106 pacific ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]625 Spooner Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]764 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]764 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]620 Baker Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]369 Independence Blvd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA[/TD]
[/TR]
[TR]
[TD]3590 Holland Rd, Ste 100 Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]101 N Lynnhaven Rd. Ste202 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3877 Holland Rd, Ste 424 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3877 Holland Rd, Ste 424 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]516 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]4107 Thistle Cir Virginia Beach VA 23462-4934[/TD]
[/TR]
[TR]
[TD]4107 Thistle Cir Virginia Beach VA 23462-4934[/TD]
[/TR]
[TR]
[TD]508 N Birdneck Rd Ste G Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]508 N Birdneck Rd Ste G Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]4554 Virginia Beach Blvd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]649 Newtown Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]6049 Indian River Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]3061 Brickhouse Court, Suite 109 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]5020 Ferrell Pkwy Num 205-163 Virginia Bch VA 23464[/TD]
[/TR]
[TR]
[TD]5020 Ferrell Pkwy Num 205-163 Virginia Bch VA 23464[/TD]
[/TR]
[TR]
[TD]701 Lynnhaven Pkwy Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]701 Lynnhaven Pkwy Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]1709 Schooner Strait Ct Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1121 Clydesdale Ln Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]Bank St Norfolk VA 11111[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]108 Smythe Ct Apt 102 Virginia Beach VA 23452-1721[/TD]
[/TR]
[TR]
[TD]108 Smythe Ct Apt 102 Virginia Beach VA 23452-1721[/TD]
[/TR]
[TR]
[TD]3030 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]1777 London Bridge Rd Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]3112 Virginia Beach Blvd Virginia Beach VA[/TD]
[/TR]
[TR]
[TD]1 Columbus Ctr # 700 Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]3615 Atlantic Ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]3565 Purebred Dr Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]1146 Rodgers St Chesapeake VA 23324[/TD]
[/TR]
[TR]
[TD]VA 23452[/TD]
[/TR]
[TR]
[TD]4241 Bonney rd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]4241 Bonney rd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]210 S. Witchduck Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]210 S. Witchduck Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]3778 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3778 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]4988 Euclid Rd Virginia Beach VA 23462[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 513"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2106 pacific ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]2106 pacific ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]625 Spooner Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]764 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]764 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]620 Baker Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]369 Independence Blvd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA[/TD]
[/TR]
[TR]
[TD]3590 Holland Rd, Ste 100 Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]101 N Lynnhaven Rd. Ste202 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3877 Holland Rd, Ste 424 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3877 Holland Rd, Ste 424 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]516 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]4107 Thistle Cir Virginia Beach VA 23462-4934[/TD]
[/TR]
[TR]
[TD]4107 Thistle Cir Virginia Beach VA 23462-4934[/TD]
[/TR]
[TR]
[TD]508 N Birdneck Rd Ste G Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]508 N Birdneck Rd Ste G Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]4554 Virginia Beach Blvd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]649 Newtown Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]6049 Indian River Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]3061 Brickhouse Court, Suite 109 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]5020 Ferrell Pkwy Num 205-163 Virginia Bch VA 23464[/TD]
[/TR]
[TR]
[TD]5020 Ferrell Pkwy Num 205-163 Virginia Bch VA 23464[/TD]
[/TR]
[TR]
[TD]701 Lynnhaven Pkwy Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]701 Lynnhaven Pkwy Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]1709 Schooner Strait Ct Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1121 Clydesdale Ln Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]Bank St Norfolk VA 11111[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]108 Smythe Ct Apt 102 Virginia Beach VA 23452-1721[/TD]
[/TR]
[TR]
[TD]108 Smythe Ct Apt 102 Virginia Beach VA 23452-1721[/TD]
[/TR]
[TR]
[TD]3030 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]1777 London Bridge Rd Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]3112 Virginia Beach Blvd Virginia Beach VA[/TD]
[/TR]
[TR]
[TD]1 Columbus Ctr # 700 Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]3615 Atlantic Ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]3565 Purebred Dr Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]1146 Rodgers St Chesapeake VA 23324[/TD]
[/TR]
[TR]
[TD]VA 23452[/TD]
[/TR]
[TR]
[TD]4241 Bonney rd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]4241 Bonney rd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]210 S. Witchduck Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]210 S. Witchduck Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]3778 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3778 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]4988 Euclid Rd Virginia Beach VA 23462[/TD]
[/TR]
</tbody>[/TABLE]