I am trying to take an address string and convert the specific address to a block number.
For example: 123 N Main Street
would become: 100 Block N Main Street
Since the address string may or may not start with a number (the address could be Main / First) and since that number could be anywhere between 1 and 6 characters long, I'm a bit stumped.
I can use an if/then/else and a numeric(left(fieldname,X)) statement to determine if it starts with a number but then I need to convert that number to blocks, strip the text it is replacing, add the word Block and then the rest of the field.
I swear I saw (and maybe even used) such a formula but I can't find it anywhere. Any help is appreciated.