Power Query help with cleaning location data

C

csenor

Board Regular
Joined
Apr 10, 2013
Messages
169
Office Version
  1. 365
Platform
  1. Windows
Good morning. I have over a million records of location data that I need to populate on a 3D map. The records have some flaws in that some are missing the city, state, zip while others have an apartment # and district code built into the middle of the record (identified as a (text string or apartment number)(semi-colon)(district letter)(comma). Is there anyway to extract this string from the middle of the record using power query in order to remove it? Thanks in advance.

Windows 10
Office 365

Book1.xlsx
E
1Location
21861 N Black Horse PIKE,122;K, Williamstown, NJ 08094
3431 Myrtle AVE,16A;V, Woodbury, NJ 08096
4N Black Horse PIKE/Crystal DR, Williamstown, NJ 08094
5322 High ST, Woodbury, NJ 08096
6718 N Delaware ST, Paulsboro, NJ 08066
7124 Holly PKY, Williamstown, NJ 08094
834 G ST, West Deptford, NJ 08096
9110 Austin CT, Deptford, NJ 08096
101 Pondview Dr,P204;X, Woolwich Township, NJ 08085
11500 Elm AVE,2;W, Woodbury Heights, NJ 08097
122 Stewart LN, Deptford, NJ 08096
13526 Union AVE, Newfield, NJ 08344
1412 Southwest BLVD
151211 N Delaware ST, Paulsboro, NJ 08066
16231 N Evergreen AVE,COMPLEX;V, Woodbury, NJ 08096
17200 Helena ST, Newfield, NJ 08344
18141 Wentz AVE, Woodbury Heights, NJ 08097
197 Styvesant DR, Swedesboro, NJ 08085
2062 S Bridgeport RD, Bridgeport, NJ 08014
21350 E Clinton ST,A;A, Clayton, NJ 08312
22102 Church ST, Swedesboro, NJ 08085
23701 Red Bank AVE,M11;T, West Deptford, NJ 08096
24125 Virginia AVE,1;K, Williamstown, NJ 08094
25307 S West AVE
264D Hawthorne Woods
27407 E High ST, Clayton, NJ 08312
28159 Democrat RD,2;C, Mickleton, NJ 08056
29159 Democrat RD,2;C, Mickleton, NJ 08056
30159 Democrat RD,2;C, Mickleton, NJ 08056
Sheet2
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
if the apt is always of the form ",*;*," where * represents one or more characters then try:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Location"]}[Content],
    AddHasSemiColon = Table.AddColumn(Source, "HasSemiColon", each Text.Contains([Location],";")),
    Part1 = Table.AddColumn(AddHasSemiColon, "AptRemoved", each if [HasSemiColon] then Text.Middle([Location],0,Text.PositionOf([Location],",")) else [Location]),
    Part2 = Table.AddColumn(Part1, "AptRemoved1", each if [HasSemiColon] then Text.Middle([Location],Text.PositionOf([Location],",",2){1}) else null),
    MergePart1and2 = Table.CombineColumns(Part2,{"AptRemoved", "AptRemoved1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"AptRemoved"),
    RemoveHasSemiColon = Table.RemoveColumns(MergePart1and2,{"HasSemiColon"})
in
    RemoveHasSemiColon

misc examples.xlsm
ABCD
1LocationLocationAptRemoved
21861 N Black Horse PIKE,122;K, Williamstown, NJ 080941861 N Black Horse PIKE,122;K, Williamstown, NJ 080941861 N Black Horse PIKE, Williamstown, NJ 08094
3431 Myrtle AVE,16A;V, Woodbury, NJ 08096431 Myrtle AVE,16A;V, Woodbury, NJ 08096431 Myrtle AVE, Woodbury, NJ 08096
4N Black Horse PIKE/Crystal DR, Williamstown, NJ 08094N Black Horse PIKE/Crystal DR, Williamstown, NJ 08094N Black Horse PIKE/Crystal DR, Williamstown, NJ 08094
5322 High ST, Woodbury, NJ 08096322 High ST, Woodbury, NJ 08096322 High ST, Woodbury, NJ 08096
6718 N Delaware ST, Paulsboro, NJ 08066718 N Delaware ST, Paulsboro, NJ 08066718 N Delaware ST, Paulsboro, NJ 08066
7124 Holly PKY, Williamstown, NJ 08094124 Holly PKY, Williamstown, NJ 08094124 Holly PKY, Williamstown, NJ 08094
834 G ST, West Deptford, NJ 0809634 G ST, West Deptford, NJ 0809634 G ST, West Deptford, NJ 08096
9110 Austin CT, Deptford, NJ 08096110 Austin CT, Deptford, NJ 08096110 Austin CT, Deptford, NJ 08096
101 Pondview Dr,P204;X, Woolwich Township, NJ 080851 Pondview Dr,P204;X, Woolwich Township, NJ 080851 Pondview Dr, Woolwich Township, NJ 08085
11500 Elm AVE,2;W, Woodbury Heights, NJ 08097500 Elm AVE,2;W, Woodbury Heights, NJ 08097500 Elm AVE, Woodbury Heights, NJ 08097
122 Stewart LN, Deptford, NJ 080962 Stewart LN, Deptford, NJ 080962 Stewart LN, Deptford, NJ 08096
13526 Union AVE, Newfield, NJ 08344526 Union AVE, Newfield, NJ 08344526 Union AVE, Newfield, NJ 08344
1412 Southwest BLVD12 Southwest BLVD12 Southwest BLVD
151211 N Delaware ST, Paulsboro, NJ 080661211 N Delaware ST, Paulsboro, NJ 080661211 N Delaware ST, Paulsboro, NJ 08066
16231 N Evergreen AVE,COMPLEX;V, Woodbury, NJ 08096231 N Evergreen AVE,COMPLEX;V, Woodbury, NJ 08096231 N Evergreen AVE, Woodbury, NJ 08096
17200 Helena ST, Newfield, NJ 08344200 Helena ST, Newfield, NJ 08344200 Helena ST, Newfield, NJ 08344
18141 Wentz AVE, Woodbury Heights, NJ 08097141 Wentz AVE, Woodbury Heights, NJ 08097141 Wentz AVE, Woodbury Heights, NJ 08097
197 Styvesant DR, Swedesboro, NJ 080857 Styvesant DR, Swedesboro, NJ 080857 Styvesant DR, Swedesboro, NJ 08085
2062 S Bridgeport RD, Bridgeport, NJ 0801462 S Bridgeport RD, Bridgeport, NJ 0801462 S Bridgeport RD, Bridgeport, NJ 08014
21350 E Clinton ST,A;A, Clayton, NJ 08312350 E Clinton ST,A;A, Clayton, NJ 08312350 E Clinton ST, Clayton, NJ 08312
22102 Church ST, Swedesboro, NJ 08085102 Church ST, Swedesboro, NJ 08085102 Church ST, Swedesboro, NJ 08085
23701 Red Bank AVE,M11;T, West Deptford, NJ 08096701 Red Bank AVE,M11;T, West Deptford, NJ 08096701 Red Bank AVE, West Deptford, NJ 08096
24125 Virginia AVE,1;K, Williamstown, NJ 08094125 Virginia AVE,1;K, Williamstown, NJ 08094125 Virginia AVE, Williamstown, NJ 08094
25307 S West AVE307 S West AVE307 S West AVE
264D Hawthorne Woods4D Hawthorne Woods4D Hawthorne Woods
27407 E High ST, Clayton, NJ 08312407 E High ST, Clayton, NJ 08312407 E High ST, Clayton, NJ 08312
28159 Democrat RD,2;C, Mickleton, NJ 08056159 Democrat RD,2;C, Mickleton, NJ 08056159 Democrat RD, Mickleton, NJ 08056
29159 Democrat RD,2;C, Mickleton, NJ 08056159 Democrat RD,2;C, Mickleton, NJ 08056159 Democrat RD, Mickleton, NJ 08056
30159 Democrat RD,2;C, Mickleton, NJ 08056159 Democrat RD,2;C, Mickleton, NJ 08056159 Democrat RD, Mickleton, NJ 08056
Sheet11
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,702
Messages
6,173,932
Members
452,539
Latest member
delvey

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