PDA

View Full Version : Some dificulties to convert text in numbers in a cell



ioncila
10-10-2010, 02:46 AM
Hi
Very frequently, i need to export data from MS Project Files to Excel.

My problem is that its a very boring and stupid (sorry for the word) routine because, in excel, i need too much steps to convert data of imported cells in numbers. Like this:

1 - Export data from Project to Excel.
2 - Select, for example, A1 to copy the range
3 - Data will be shown as "300 days" in A1, "8 days" in A2, and so on, in text mode.
4 - Now I need to convert data in numbers. In another column I write the following formula - "=Left(A1;NUM.CARACT(A1)-4)". It deletes the word "days", but data is still text.
5 - Then I have to convert that data in number values through the most known excel routine (writing "1" in another cell, copy, selecting the range, copy special, multiplication).
6 - Finally, select the converted range and substitute values in A1

Is there a way or a formula to execute that in a quicker, simplier and shorter way?
I will be so glad if there is.

Thank you very much
Ioncila

Bob Phillips
10-10-2010, 03:04 AM
Try using

=--(Left(A1;NUM.CARACT(A1)-4))

ioncila
10-10-2010, 03:28 AM
Hi. Thank you very much for your fast response (I'm not the only one working on a sunday :) ).
It works.
While waiting for help, I found, in a pure unexpected and lucky way, a similar result: "=Left(A1;NUM.CARACT(A1)-4)*1".

Now the perfect picture is to find, in project, an automatic way to export data as numbers.

Once again, thank you for your precious help.
Ioncila

ioncila
10-10-2010, 03:29 AM
Hi. Thank you very much for your fast response (I'm not the only one working on a sunday :) ).
It works.
While waiting for help, I found, in a pure unexpected and lucky way, a similar result: "=Left(A1;NUM.CARACT(A1)-4)*1".

Now the perfect picture is to find, in project, an automatic way to export data as numbers.

Once again, thank you for your precious help.
Ioncila

Bob Phillips
10-10-2010, 05:31 AM
If you can get rid of the text part, the days, in Project, when you impirt to Excel it should get treated as numbers.