The state abbreviation in the original notes field is always two letters, always in CAPS, and is always followed by the zip code.
Then you could use
MiddleWords ( OldDBNotes ; WordCount ( OldDBNotes ) - 1 ; 1 )
provided you already managed to isolate that block.
Alternatively, try a Custom Function:
// FindStateAbb ( theText )
Let ( [
$i = $i + 1 ;
textAsList = Substitute ( theText ; " " ; ¶ ) ;
listOfFoundAbbs = FilterValues ( textAsList ; "MT¶SC¶WA" ) ; // etc. … or create a list of State abbreviations as value list, Custom Function or in its own table – always handy to have, I gather – and pass a reference here
countOfFoundAbbs = ValueCount ( listOfFoundAbbs ) ;
thisFoundAbb = GetValue ( listOfFoundAbbs ; $i )
Case ( Exact ( thisFoundAbb ; Upper ( thisFoundAbb ) ) ;
Let ( $i = "" ; thisFoundAbb ) ;
Case ( $i < countOfFoundAbbs ; FindStateAbb ( theText ) )
or create a script out the logic – straight looping is easier than recursion.
is = "1044 Johnnie Dodds Blvd #L3
Mt Pleasant SC 29464"; // whatever your address is; is also means isolate state
f1 = Filter(Substitute(is;" ";¶); "ABCDEFGHIJKLMNOPQRSTUVWXYZ¶");
myStateList = List("AA";"AB";"SC") /* ...... */ ]; // all the state codes inhere, can be calced outside and referenced
FilterValues(myStateList ; f1)
You've lost me...how does this calc tell me where the two capital letters 'SC' are located in the OldDBNotes field? And how does it prevent me from finding 'Mt' instead of 'SC'?
Would that be the first calc or the second one?
The first one. : - )
Well, I amended my post; so the idea is that if you are able to isolate the address block, then you could pull out the abbreviation by position – whatever that abbreviation may be.
All in all, you're not really interested in the position - that is only a means to an end. You want to get that abbreviation.
But I think the second calculation is better, because it could be applied to whatever you feed it – if there is a standalone two-letter string that matches one of the abbreviations and is in UPPERCASE, it is returned – or more to the point, the first occurrence of such a beast is returned (so you will get SC, not Mt).
PS: Siplus's solution is better, because he uses Filter() to chop away the lower-case stuff, and thus needs no recursion.
Good idea …