Hi folks,
I have addresses like this:
123 Sample Street
456 Example Road
and some like this:
123 Sample St
456 Example Rd
To clarify, there are multiple entries with the same addresses but different โendingsโ.
I plan to clean them up at some point but for now Iโm taking the first two substrings so I can compare them in a filter, like โ123 Sampleโ and โ456 Exampleโ
I was just wondering if there might be a more straightforward way of doing it than as used below (whic seems to work fine):
LINKTOFILTEREDVIEW( "History View" ,
CONCATENATE(
INDEX(
SPLIT( [Street], " " ),
1
),
" ",
INDEX(
SPLIT( [Street], " " ),
2
)
)
=
CONCATENATE(
INDEX(
SPLIT( [_THISROW].[Street], " " ),
1
),
" ",
INDEX(
SPLIT( [_THISROW].[Street], " " ),
2
)
)
)
AppSheet is no good dealing with this type of situation. No matter what you try with what AppSheet can do, youโll be frustratedโthere are simply waaaaayyyy too many ways the same street address can be expressed that confound the limited text processing capabilities of AppSheet.
Were I in your position, and I was using a Google Sheet as my data source (or maybe Excel-I donโt know if Excel has the function), I would consider adding another column to the sheet with a spreadsheet formula that uses REGEXREPLACE() to attempt to produce a value with some degree of cleanup. It wouldnโt be trivial, and would be very error-prone.
Thank you for your reply Steve.
And just wondering: Is the double CONCATENATE formula Iโve used a reasonable approach to retrieving a subset of substrings from a string, in this case the first two substrings of an address? Just curious whether thereโs a simpler way.
Yes, your expression will in fact compare the first two words from the two columns.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |