MailMerge field for decimals with letters, i.e. 1A.23

[scribbled]

I have two format possibilities for data in an Excel column: ##.##, or #*.## (i.e. 1.23 or 4A.56).



If the mergefield is used with no switches then 4A.56 shows as expected, but 1.23 will show with repeating decimals (1.2222222222223). If I use the \# 0.00 switch then 1.23 shows as expected, but the letter is stripped out of 4A.56 (4.56, I also tried \# 00.00).

The core of the issue appears to be that the column is formatted as General in Excel. When I switch that to Text and reload the data source the mergefield with no switches shows both values as expected. The problem is that I cannot change the Excel data source for practical use. This file is being generated by a third-party application that exports the column as General and the datasource is overwritten every time.

Another wrinkle is that this only happens on Windows, which is also the only OS the application is available on. When I load the mail merge doc and the data source on my Mac, it shows both values as expected with no changes to either file.

After hours of playing around, googling, and trying different things, I'm assuming this is just not possible, but I wanted to exhaust all resources before moving on. I haven't gotten it to work using DDE, but it's a pretty janky solution, and I would love for this to be fixable in the document itself.

I've attached a copy of the document and data source. The fields are ItemNumber and SubLocation.

Any insight would be greatly appreciated.

Thanks

[Guessed]

Looking at what you provided, it appears that you could compare SubLocation against a formatted ItemNumber. Do you have a larger data set which includes rounded numbers for us to test on?

I did have a fiddle with the one entry and you might be able to try something along the lines of testing formatted vs unformatted eg
{If {MERGEFIELD ItemNumber \# ##.##} = {MERGEFIELD ItemNumber } {MERGEFIELD ItemNumber } {MERGEFIELD ItemNumber \# ##.##}}

[scribbled]

Thanks so much for the reply. I will play around with this in the morning.

As far as an extended data set goes, ItemVolume and ItemIssue will likely always have values with both letters and numbers, but I think that's it. To expand a bit, I included values in both ItemNumber and SubLocation just as examples of what the two formats would look like. The numbers themself would only ever be in the SubLocation of the export.

[scribbled]

Originally Posted by Guessed

I did have a fiddle with the one entry and you might be able to try something along the lines of testing formatted vs unformatted eg
{If {MERGEFIELD ItemNumber \# ##.##} = {MERGEFIELD ItemNumber } {MERGEFIELD ItemNumber } {MERGEFIELD ItemNumber \# ##.##}}

So, this actually did work, thank you so much for taking the time. Unfortunately, while it does work when I'm manually opening Word and selecting the data source, for some reason it still doesn't work when Word is using a freshly generated data source from the application.

Oh well, I think it's time to move on.

Thanks again