### 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.

=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

SUBSTITUTEfunction replaces a set of characters with another.The syntax for the

SUBSTITUTEfunction is:SUBSTITUTE( text, old_text, new_text, [nth_appearance] )

textis the original string to use to perform the substitution.old_textis the existing characters to replace.new_textis the new characters to replace old_text with.nth_appearanceis 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

TRIMfunction returns a text value with the leading and trailing spaces removed.The syntax for the

TRIMfunction is:TRIM( text )

textis the text value to remove the leading and trailing spaces from.In Microsoft Excel, the

LENfunction returns the length of the specified string.The syntax for the

LENfunction is:LEN( text )

textis the string to return the length for.In Microsoft Excel, the

MODfunction returns the remainder after a number is divided by a divisor.The syntax for the

MODfunction is:MOD( number, divisor )

numberis a numeric value whose remainder you wish to find.divisoris the number used to divide thenumberparameter. If thedivisoris 0, then theMODfunction will return the #DIV/0! error.In Microsoft Excel, the

MIDfunction extracts a substring from a string (starting at any position).The syntax for the

MIDfunction is:MID( text, start_position, number_of_characters )

textis the string that you wish to extract from.start_positionindicates the position in the string that you will begin extracting from. The first position in the string is 1.number_of_charactersindicates the number of characters that you wish to extract.