+ Reply to Thread
Results 1 to 9 of 9

Converting a range of numbers stored as text to number format w/o looping

  1. #1
    Forum Contributor
    Join Date
    12-15-2009
    Location
    Herndon, VA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Converting a range of numbers stored as text to number format w/o looping

    I have numbers in E2:E3265 that have been downloaded from a database, but they were downloaded in text format. I want to convert all of these to number format. I know you can do this by multiplying by 1. I know how to do this for a single cell, but can this be done for a range of cells w/o using a loop?

    This isn't working:

    Range("E2:E326").Value = Range("E2:E326").Value * 1

  2. #2
    Forum Contributor
    Join Date
    03-12-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2013(work), Excel 2013(home)
    Posts
    226

    Re: Converting a range of numbers stored as text to number format w/o looping

    You might be able to use the paste special methods.
    If you can't figure out how a formula works, try stepping through it using "Evaluate Formula" in the Formula Auditing menu item in the tools menu!

    If you want to see where your code went wrong, try stepping through it by clicking in the code and pressing F8 and watch as the magic happens!


    If you are happy with any of the results, please add to the contributor's reputation by clicking the star icon.

  3. #3
    Forum Contributor
    Join Date
    03-12-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2013(work), Excel 2013(home)
    Posts
    226

    Re: Converting a range of numbers stored as text to number format w/o looping

    i.e. http://www.mrexcel.com/forum/excel-q...el-2010-a.html

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Converting a range of numbers stored as text to number format w/o looping

    Assuming that the value of 1 is in B1.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Converting a range of numbers stored as text to number format w/o looping

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    12-15-2009
    Location
    Herndon, VA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Converting a range of numbers stored as text to number format w/o looping

    Quote Originally Posted by Kenneth Hobson View Post
    Please Login or Register  to view this content.
    Wow. It worked. I can't believe it was that easy.

    It looks like all you have to do is restate the value?

  7. #7
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Converting a range of numbers stored as text to number format w/o looping

    Same method for converting formulas to static values. Sometimes life is easy.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,279

    Re: Converting a range of numbers stored as text to number format w/o looping

    If the cells are formatted as text, you could also use:

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,279

    Re: Converting a range of numbers stored as text to number format w/o looping

    Thanks for the rep



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-23-2011, 06:36 PM
  2. Conditionally format numbers stored as text
    By jimojimo in forum Excel General
    Replies: 6
    Last Post: 04-28-2010, 11:45 AM
  3. Converting Numbers Stored as Text
    By mremmenga in forum Excel General
    Replies: 3
    Last Post: 10-09-2008, 03:28 AM
  4. converting numbers stored as text to numbers...
    By firefiend in forum Excel General
    Replies: 3
    Last Post: 02-08-2007, 03:33 PM
  5. Converting numbers stored as dates to text in Excel
    By David from Furdale in forum Excel General
    Replies: 1
    Last Post: 07-17-2006, 01:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1