AnsweredAssumed Answered

Export to excel formatting

Question asked by bablackburn on Jan 7, 2019
Latest reply on Jan 8, 2019 by bablackburn


So this might be a question as much about excel as it is about filemaker as I'm not sure where the easiest point to correct this is. In our office we export data to an excel spread sheet to pass off to a third party to check. Once it's checked we import the data into our expense tracking database. The problem is the job number field in excel removes the dashes and it takes time to readd them as they need to be in the style of "##-####" or the expense tracking won't recognize them. Formatting the cells only adds the dash but doesn't change the value, therefore the only way I've found is to manually add the dash in every line which can sometimes be several hundred rows. In the Invoice number column the data is stored as text and therefore keeps any dashes or specific formatting without changes. I don't know why invoice number is stored as text but job number wouldn't be. On the purchase orders side the invoice number is typed in manually while the job number is a looked up value from a different database.

Screen Shot 2019-01-07 at 12.51.44 PM.png

 

Screen Shot 2019-01-07 at 12.49.38 PM.png

In cell A2 I've shown what happens when manually entering the dash. The value is changed to store as text and left justifies just as the Invoice Number in column E. When automatically formatted though as in cell A3 the cell changes while value remains the same and stores as a number.

 

Is there any way to make sure the dash doesn't get removed in the initial export and if not how do I format the spreadsheet to add the dash and also store as text?

Outcomes