Consulting

Results 1 to 5 of 5

Thread: Some dificulties to convert text in numbers in a cell

  1. #1
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location

    Some dificulties to convert text in numbers in a cell

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Try using

    =--(Left(A1;NUM.CARACT(A1)-4))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location
    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

  4. #4
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location
    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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    If you can get rid of the text part, the days, in Project, when you impirt to Excel it should get treated as numbers.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •