FIND Expression Help

[HeathBiller]

Hello,

I am looking for help with an expression. I have a data set with full addresses and am trying to filter out the two-letter State abbreviation from that data. This is my current formula that works if the address is entered with only one comma.  Example: 123 First Street Detroit, MI 48220.

(text(mid([Address],find(",",[Address])+2,2)))

The problem is that when an address is entered into Appsheet the recommended address format has two commas.  Example: 123 First Street, Detroit, MI 48820

Any recommendations on how to get the FIND expression to start at the second comma instead of the first comma? Thanks in advance, I appreciate the help as always.

[Suvrutt_Gurjar]

Does this help. There could of course be  more options and scope to make it more compact.

INDEX(SPLIT(SUBSTITUTE([Address],",","")," "," , "),COUNT(SPLIT(SUBSTITUTE([Address],",","")," "," , "))-1)

Edit: if the Zip code is always going to be 5 digits, then a simpler format, something like below would do

LEFT(RIGHT([Address], 8),2)

[HeathBiller]

Your first formula kind of worked. It gave back the five-digit zip code though instead of the two-letter state abbreviation. So there must be something slightly off but I can't figure out what.

I went ahead and used the simpler version though. I will just let people know they have to enter just the 5-digit zip code if they want to have the State initials automatically calculated.  Addresses entered in Appsheet include another comma and USA, "123 First Street, Detroit, MI 48820, USA" so I adjusted the formula below. In case anyone else reading this later is wanting to use the same formula. Thanks for the help.

LEFT(RIGHT([Address], 13),2)

[Suvrutt_Gurjar · Accepted solution]

Thank you for the update. 

---

@HeathBiller wrote:

Your first formula kind of worked. It gave back the five-digit zip code though

---

---

@HeathBiller wrote:

So there must be something slightly off but I can't figure out what.

---

Sure, no problem,  I will elaborate.

The formula was perfect for the given requirement. It gave 5 digit zip code because you tested it with the word USA added to the address which you had not mentioned in original requirement.

The formula splits the address into a list and selcts last but one list element. Since you added USA to the address the last but one element shifted to Zip code as it became the second last element.

Please test with 

INDEX(SPLIT(SUBSTITUTE([Address],",","")," "," , "),COUNT(SPLIT(SUBSTITUTE([Address],",","")," "," , "))-2)

and it should work.The revised expression selects third last element.

The addition of word USA was the precise reason that required adjusting the second expression as well. 

For anyone reading this thread, one can see that even seemingly minor missing  point in requirement can impact the result.

[hocappsheet_vn]

index(split([[Address],", "),2)