Compare Address with Different Street Abbreviations

[Awat]

Hi,

I compare addresses using StrCmp(str1, str2, 1). I am able to omit the case-sensitivity but am having a trouble with street abbreviations such as Ave, Ave., Avenue.

How can I omit the differences so the formula would return 0 (equal)?

Thanks.

 

[Turkbear]

Hi,
Depending on the structure of the data in that field, you might be able to compare only the Major portion of the address - for instance if the address is always
Number StreetName TypeOfStreet City State zip
123 Thomas Ave. Boise ID xxxxx

By using the InStr and MID functions you could compare only the Number, Street and Zip which might meet your needs.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could remove the street designations by creating a formula like this:

replace(replace(replace(replace({table.address1},"Ave",""),"Ave.",""),"Avenue",""),"St","")

You can add more values to replace following the above method. Then use the formulas in your string compare function instead of the fields.

-LB

 

[Awat]

Thanks, Turkbear & LB

Turkbear, unfortunately, I don't have the separate portions of address. No., St name and type are in the same field. Thanks anyway for your help.

LB, the formula works like a charm, thanks! Just one little change , it has to start with the longest string otherwise the first replace would cut "Avenue" into "Nue" as a result the replace "Avenue" wouldn't work.

replace(replace(replace(replace({table.address1},"Avenue",""),"Ave.",""),"Ave",""),"St","")

Thanks again

 

[lbass]

Good point. Glad you caught the error.

-LB

 

[lbass]

On second thought, instead of removing the designations, maybe you should be replacing abbreviations with full words, so that you can catch differences when one address says "Street" versus "Avenue".

-LB