Question

"Write from data table" is always TEXT in Excel

  • 10 January 2024
  • 4 replies
  • 83 views

Badge +1

Hello,

When I write from data table into excel worksheet the numbers are stored as text.  I need them to be numbers so I can put in formulas and have the calculations work properly.  I have tried several things.

  1. Changed format of all cells to ‘number’ (didn’t work).
  2. Made sure I was using .xlsx and not .csv file.
  3. Changed column type on data table to ‘numbers’ and then did write from data table (didn’t work).
  4. Turned off the error checking in Excel Options (didn’t work).

What am I doing wrong??  How do I fix?


4 replies

Userlevel 7
Badge +13

 

 

Hi @Ken 3236 ,

 

You can follow any of the below methods to resolve this issue.

 

Refer the Fix text-formatted numbers by applying a number format - Microsoft Support article 

 

Or

 

Try after disabling Excel Auto checking option. Follow the below steps to disable the same.

 

  1. Under Excel menu, go to the Options.
  2. Select Formulas.
  3. Uncheck the Enable background error checking option.

 

 

Before the changes

 

 

After the changes

 

 
 
 
Or
 
 
The easiest way is to have an empty excel with the name temp.xlsx and set the column formats as required in your output excel. Now simply write range your Data Table in it and rename it.
 
For e.g. if you want to preserve your first column as number then in temp.xlsx set it as number and when you write the data table to it, your format will be preserved.
 
 
 
 
 
Hope this helps.
 
Badge +1

None of that worked for me.  I did find out that if I use a data table I can loop through and set cell by cell which preserves the number format.  Appreciate the help!

Userlevel 2
Badge +8

Try using Get Multiple Cells to get all the data. Make sure in this action you are reading cell values and NOT visible text in cell.

Userlevel 2
Badge +8

Try using Get Multiple Cells to get all the data. Make sure in this action you are reading cell values and NOT visible text in cell.

Sorry, misunderstood. You’re going OUT to excel not in from excel...

Reply