MS Project to Excel — Text Error

Question

Hello,

Hope you all are well!

I've been having a very odd issue when importing data from Project into Excel...

Unique ID	ID (line #)	% Complete	Duration

The % Complete field in Excel is formatted as a Percent (no decimals). However, when I import data from Project (copy+paste), some of the tasks receive an error stating "Number Stored as Text"

Why some percentages are being "stored as text" is a mystery to me. What's even more interesting is that, when I copy "% Complete" from Project and Paste Special as "Text" in Excel, I receive no errors... and the cells in Excel are still formatted as Percentages.

Any help with this would be greatly appreciated!

Cheers,
James

Answer 1

Hello,

I’m Shannah and I’d be happy to help you with your question. In this forum, we are Microsoft consumers just like yourself.

It sounds like you're having trouble formatting cells to display percentages in an Excel worksheet.

In some cases, numbers in a worksheet are actually formatted and stored in cells as text, which can cause problems with calculations or produce confusing sort orders. This issue sometimes occurs after you import or copy data from a database or other external data source.

One possible solution could be to check if the data in the cells is stored as text instead of numbers. If the data is stored as text, Excel will not recognize it as a number and will not apply number formatting, such as currency or percentage. You can check if the data is stored as text by selecting one of the cells and looking at the formula bar. If the data is stored as text, it will be left-aligned in the cell and in the formula bar.

If the data is stored as text, you can convert it to numbers by selecting the cells, clicking on the Data tab, and then clicking on Text to Columns. In the Convert Text to Columns Wizard, select Delimited, click Next, clear all the delimiter check boxes, and then click Finish.

I hope this helps!

Regards, Shannah

Answer 2

Hi Shannah,

Thanks so much for your response!

I understand the steps I would take to re-format the cells once I've already copied them over . . . The question I have is — Why am I receiving an error to begin with? And why does the error only appear on some of the tasks?

• All the data in Project is coming from the "% Complete" column (a default/hard-coded field)... which means it cannot be formatted as anything other than a percentage
• All the cells they are being copied to in Excel are formatted as percentages
• The errors share no visible commonality... meaning they appear to be random

Thank you,
James