Convert address into codes

AppSheet AppSheet Q&A
1

Hello,

How would one write an expression (if even possible) to convert a street address to a custom code.

123 Streetname Place

To

“STRE123”

So like extract 4 letters from the left of an address,
Extract all numbers and place them on the right of the text.

2

Yeah, that’s all I got up to.

My brain power and… intillegince only gets me so far:

SPLIT(LEFT([Location], (FIND(" ", [Location]) - 1)), " ")

This extracts the numbers ok, I believe the last step would be to add in a filter to get the text and place it on the right but no idea where to go. At least, I don’t understand how to implement Index for this

3

Not quite the direction I imagined.

For the numbers:

INDEX( SPLIT( [Location] , " " ) , 1 )

For the first 4 letters of the street name:

LEFT( INDEX( SPLIT( [Location] , " " ) , 2 ) , 4 )

Combine them with & or CONCATENATE()

Of course none of this takes into account user-typos, street names less than 4 characters, etc…

2 Likes
4

Marc_Dillon:

Of course none of this takes into account user-typos, street names less than 4 characters, etc…

Yep.

I understand what you’re trying to do, @MKau, but I suspect it will end up being a huge problem over time. There are so many ways the code generation process could break that are completely outside your control and ability to predict (I would imagine). I would encourage you to consider some other path.

3 Likes
5

Thanks Steve,

It’s mainly data entry so I’m ok if it breaks. When I’m on the road it’s an incredible timesaver to have Appsheet fill in my Google Sheet entries.

I marked the solution and wanted to thank Marc for this code. I’ve analysed it and learnt a great deal on how to improve on writing this kind of stuff now.

Cheers

3 Likes
6

SUBSTITUTE(UPPER(CONCATENATE(LEFT( INDEX( SPLIT( [Location] , " " ) , 2 ) , 4 ),INDEX( SPLIT( [Location] , " " ) , 1 ) )), “/”, “”)

3 Likes