We all struggle with the same issue when exporting data from Primavera P6 to Microsoft Excel for analysis, progress updating and graphing. Excel does not seem to recognize numbers as numbers!
That is because P6 formats all data exports as text (appends an apostrophe in front of all data).
This makes getting clean data from P6 (even filtered data) extremely difficult. Often we would like to manipulate the data in Excel (such as performing calculations in Excel using P6 numeric, date or cost data).
While you might think this has an easy fix in Excel, using the “Format Cells” function will not change the text data to the data format you require. You would actually have to go through each cell individually to change it from text to number!
This following “secret” tip is an even quicker and easier way to solve this issue using multiplication. Watch the video below to learn how:
When exporting data from Primavera P6 to Excel, we want to make sure that Excel recognizes numbers as numbers.
I exported a large set of data from P6 into Excel and as you can see, the numbers are positioned to the left in their fields – meaning, they are formatted as text not numbers. If we want to make that correction, we have to go through each cell individually and hit “Enter” to transform it into a number format.
The “Number Formatting” options in Excel also do not resolve this.
But I have got a quick tip for you!
We are going to multiply all of these numbers by one to transform them into numbers.
I start by adding “1” to any empty cell, hit copy, and select all of the cells with the number values we want to convert.
We then right-click and go to “Paste Special” and go down in the menu to “Paste Special” again to get the pop-up box.
From here, we select the “Multiply” option in the “Operation” section and hit “Ok”.
And this is it! Simple as that.
Another tip I can share with you is using Excel’s “Text to Column” option on the Data tab.
To change the format of exported duration, date, or cost fields from text to the proper data format, start by selecting the column (only select one column at a time).
Here I selected Column E (Original Duration). After selecting your desired column, on the “Data” tab.
In the Data tab, look within the “Data Tools” group and click on “Text to Columns”
Next in the “Convert Text to Columns Wizard – Step 2 of 3” dialog, select the “Tab” checkbox under the Delimiter group. In the “Text qualifier” drop-down box, select the apostrophe “’” and click on “Next”.
Then, in the “Convert Text to Columns Wizard – Step 3 of 3” dialog do not change any of the default settings but check to confirm the following and click “Finish”.
- Under “Column data format” grouping, ensure you selected “General”
- In the “Destination” textbox, ensure that the column shown is the same as the one selected.
We successfully converted the data in Column E (Original Duration) to numbers with the apostrophes removed.
I hope you found this tip useful. It has certainly helped me!
