3 Replies Latest reply on Feb 12, 2015 6:45 PM by micagordon

# Need Help Converting Excel formula to Filemaker

### Title

Need Help Converting Excel formula to Filemaker

### Post

Good morning. I found a formula for Excel that converts an ISBN 13 number to ISBN 10 that I would love to implement in my database. But I don't know that much about formulas and wondered if someone might be able to help me convert this excel formula to work with filemaker.

=SUBSTITUTE(TRIM(SUBSTITUTE(MID(A2,4,LEN(A2)-4),"-"," "))," ","-")&IF(MID(A2,LEN(A2)-1,1)="-","-","")&SUBSTITUTE(MOD(990-SUMPRODUCT((11-ROW(\$1:\$9))*MID(MID(SUBSTITUTE(A2,"-",""),4,9),ROW(\$1:\$9),1)),11),10,"X")

This would take an ISBN 13 number like 9780393040029 and convert it to 0309304002X

Thank you for your help.

• ###### 1. Re: Need Help Converting Excel formula to Filemaker

=SUBSTITUTE
(TRIM
(SUBSTITUTE
(MID(A2,4,LEN(A2)-4),"-"," "))," ","-")
&IF(MID(A2,LEN(A2)-1,1)="-","-","")&SUBSTITUTE(MOD(990-SUMPRODUCT((11-ROW(\$1:\$9))*MID(MID(SUBSTITUTE(A2,"-",""),4,9),ROW(\$1:\$9),1)),11),10,"X")

In Microsoft Excel, the SUBSTITUTE function replaces a set of characters with another.
The syntax for the SUBSTITUTE function is:
SUBSTITUTE( text, old_text, new_text, [nth_appearance] )
text is the original string to use to perform the substitution.
old_text is the existing characters to replace.
new_text is the new characters to replace old_text with.
nth_appearance is optional. It is the nth appearance of old_text that you wish to replace. I f this parameter is omitted, then every occurrence of old_text will be replaced with new_text.

In Microsoft Excel, the TRIM function returns a text value with the leading and trailing spaces removed.
The syntax for the TRIM function is:
TRIM( text )
text is the text value to remove the leading and trailing spaces from.

In Microsoft Excel, the LEN function returns the length of the specified string.
The syntax for the LEN function is:
LEN( text )
text is the string to return the length for.

In Microsoft Excel, the MOD function returns the remainder after a number is divided by a divisor.
The syntax for the MOD function is:
MOD( number, divisor )
number is a numeric value whose remainder you wish to find.
divisor is the number used to divide the number parameter. If the divisor is 0, then the MOD function will return the #DIV/0! error.

In Microsoft Excel, the MID function extracts a substring from a string (starting at any position).
The syntax for the MID function is:
MID( text, start_position, number_of_characters )
text is the string that you wish to extract from.
start_position indicates the position in the string that you will begin extracting from. The first position in the string is 1.
number_of_characters indicates the number of characters that you wish to extract.

• ###### 2. Re: Need Help Converting Excel formula to Filemaker

There is really no way to convert your excel formal because filemaker does not have a SUMPRODUCT function.  I was able to obtain the answer by using other means in filemaker.   I tested about five different books with the correct results.  I recommend you test and if you find any errors please notify me.  This took some time to figure out.  I have never used the SUMPRODUCT function in excel, so I had to figure that out and check out other formulas on the internet.  Remember this app is a 1.0 version so use at your own risk.

https://www.sugarsync.com/pf/D9559058_790_868748394

I have updated this sample app with 2 layouts.  isbn 13 to 10  and  isbn 10 to 13.    I would still test both methods.

• ###### 3. Re: Need Help Converting Excel formula to Filemaker

Filemaker seems have no such function, so I think maybe Excel ISBN Barcode Add In is better to creating isbn code in excel.