Convert address into codes

[MKau]

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.

[Marc_Dillon]

help.appsheet.com

LEFT()

Left-most characters of text

help.appsheet.com

SPLIT()

List from text

help.appsheet.com

INDEX()

One specific list item

[MKau]

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

[Marc_Dillon]

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…

[Steve]

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.

[MKau]

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

[MKau · Accepted solution]

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