Converting Number In Text To Actual Numbers

  • I've been given a spreadsheet which comes out of a time tracking application. The spreadsheet list projects and resources(people), and the time expected and acutally spent. Currently a lot of manual changes are being done once this report is generated. The report is always in the same format but because of the number of resources the rows specific data appears will vary from month to month.

    Row 1 = Project Name
    Row 2 = Resource Name 1 (this row also contains the hours for resource1)
    Row 3 = Resource Name 2 (this row also contains the hours for resource2)
    Row 4 = Totals (totals up the number of resource hours for each week)

    My problem is that the values reported are in a Text Format. I've created a worksheet thats grabbing the data from the original report, but when I attempt to sum the totals for each week to get the totals for the month I get 0 because the values on the main worksheet are text, also I want the user to be able to paste in the next months report without having to convert manually (hoping I can do a conversion formula on my raw data worksheet). I'm using a lot of IF formulas to pull specific data from the report, and I've pulled over my project names and the totals per week, but now due to the conversion problem I can't sum for the month.

    I've used a similar formula in the past but its for an opposite situation (number to text) =TEXT(A1,"0000000000"), is there a formula that converts it to numeric form? sorry I couldn't post an example.

    Thanks in advance for any help.

    Special Note: I hope the Title was accurately written for this post, tried to make it descriptive enough, thanks again for your help.

  • Re: Converting Number In Text To Actual Numbers


    Consider using the VAL function.

    What you perhaps do as well, is put a zero in a cell, then Copy, Paste Special > Add, over the 'textnumbers'.




    Excel MVP 2011-2014

    For more Excel memes: visit ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!