Forum Discussion
Extract Address from Cell Text
Background: I'm working with address data that uses the quadrant system (ends with NW, NE, SW, or SE). There are only very few instances where it ends with N or S. I have a column of data that...
It's better to have samples of possible text variants from which to extract address. With some assumptions with Power Query it could be
query is
let
Source = Excel.CurrentWorkbook(){[Name="source"]}[Content],
quadrants = {" NW", " NE", " SW", " SE"},
GetAddress = Table.AddColumn(
Source,
"Address",
each Text.Start( [Text] ,
List.Max(
List.Transform( quadrants, (q) => Text.PositionOf([Text], q, Occurrence.Last, Comparer.OrdinalIgnoreCase) )
) + 3
)
)
in
GetAddress