Skip to content
+1-888-319-3663

COMMUNITY FORUM

Format Excel output to change numbers to text and display as currency

Karen asked 9 months ago
My map output is an Excel file.  The columns are all displaying as Text.  I need to convert some of the columns to numbers, and set the number format to currency. 
I found some tips in Sherry Whitten’s 7/20/17 post (https://www.eonesolutions.com/discussion/formatting-an-exported-excel-spreadsheet/), but I’m not sure how/where to apply them.  Here are the tips she shared:
‘Format columns with correct number format 
xlWorkSheet.Columns(“Q:V”).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
 xlWorkSheet.Columns(“N:O”).NumberFormat = “#,##0_);[Red](#,##0)”

‘Format numbers stored as text to numbers
xlWorkSheet.Columns(“Q:V”).Value = xlWorksheet.Columns(“Q:V”).Value
xlWorkSheet.Columns(“N:O”).Value = xlWorksheet.Columns(“N:O”).Value
 
Answers
Lorren Zemke Staff answered 8 months ago
Karen,

The formatting process would be a Post document Script task that uses the Excel Interop code to open the file and basically run the steps Sherry shared.  It’s like running a macro but it happens within the script logic that is written.
 
Lorren

If you would like to submit an answer or comment, please sign in to the eOne portal.