(MID(A2,4,LEN(A2)-4),"-"," "))," ","-")
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.
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.
I have updated this sample app with 2 layouts. isbn 13 to 10 and isbn 10 to 13. I would still test both methods.