set default answer when zipcode extract is not a number

[MrsFixIt]

my issue is getting the default zip code of 99999 answers when the extracted is not a number. I tried isnumber, but it kept rejecting with too many arguments message

 

[6StringJazzer]

You do not need to explicitly check for <>"SAME" in your logic. If you reach that part of the IF, you already know it is not "SAME". You had the right idea with ISNUMBER but you have to first convert the string to a value.

Try this

=IF([@ALTAddress]="SAME",[@Zip],IF(ISNUMBER(VALUE(RIGHT([@ALTAddress],5))),RIGHT([@ALTAddress],5),99999))

 

[Peter_SSs]

Another option

=IF([@ALTAddress]="SAME",[@Zip],IFERROR(RIGHT([@ALTAddress],5)+0,99999))

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You should also update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MrsFixIt]

You do not need to explicitly check for <>"SAME" in your logic. If you reach that part of the IF, you already know it is not "SAME". You had the right idea with ISNUMBER but you have to first convert the string to a value.

Try this

=IF([@ALTAddress]="SAME",[@Zip],IF(ISNUMBER(VALUE(RIGHT([@ALTAddress],5))),RIGHT([@ALTAddress],5),99999))

this is cool and I will give it a try.

 

[MrsFixIt]

Another option

=IF([@ALTAddress]="SAME",[@Zip],IFERROR(RIGHT([@ALTAddress],5)+0,99999))

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You should also update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

View attachment 25995

this is neat and I will also try this. thanks for the recommendations. what does CODE represent?

 

[6StringJazzer]

what does CODE represent?

CODE is a tag used around formulas to format it to make it more readable and color-code different elements. You can do this by selecting your formula then clicking the "fx XLS" button in the edit controls.

 

[Peter_SSs]

@6StringJazzer
While on codes & edit buttons, I notice that your signature includes a button whose image is now different. You may want to update your signature?