Any better way to compare concatenated substrings?

[GGL]

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
      )
   )
)

[Steve]

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.

See also:
https://support.google.com/docs/answer/3098245

[GGL]

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.

[Steve]

Yes, your expression will in fact compare the first two words from the two columns.