Mergefield format as fraction

[ebrooks54]

I'm stumped on this one. I have an Excel spreadsheet which is to be used to generate a catalog. There is a column named 'Width' that contains a number, ie 6.125. The author of the spreadsheet (not me) formatted the column so that it displays as '6 1/8'.

This is the way the enduser would also like to see it in the catalog. By default however, merging does not bring in the format. Does anyone know if there is a way to format a number as a fraction as part of a mailmerge in Word?

(I tried using a DDE connection to the spreadsheet, but it requires too much effort. IE, the file I have been given has multiple tabs, and no named ranges. DDE doesn't seem to like that.)

Thanks

 

[macropod]

Hi,

If your source data always have the same denominator for the fraction (i.e. '8' as per your example), you could try:
{QUOTE{=INT({MALIMERGE Data})}{=MOD({MALIMERGE Data},1)*8}"/8"}
with the {=MOD({MALIMERGE Data},1)*8} part of the field superscripted and the final '8' subscripted.

If the denominator varies, it could possibly still be done with field maths, but its then going to be a lot more involved to work out both the numerator and denominator.

Cheers

 

[ebrooks54]

After fiddling around with this, the simplest solution was to write a macro in excel to insert a column that converted the decimal value into a text value. Example as follows:

If "Width" is in Column C, insert Column B as "F_Width" and use formula, =Text(C2,"# ?/??"). In Word use "F_Width" as the MergeField.

Note: for completeness, and to take care of extraneous spaces and the possiblity of a width of zero value, the complete formula could be:

=IF(C2<>0,TRIM(TEXT(C2,"# ?/??")),"")