Results 1 to 5 of 5

Thread: Converting MANY columns and rows from Text to Number

  1. #1

    Converting MANY columns and rows from Text to Number



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys,

    I am getting data supplied from my database as .xlsx and when it is opened most of the cells are text, so they all need to be converted to number to be able to used in many of my formulas/vlookups etc.
    Click image for larger version. 

Name:	Microsoft Excel - SewerMainsConq.xlsm_2014-02-11_09-08-51.jpg 
Views:	4 
Size:	41.1 KB 
ID:	2042


    What I am finding is that if I highlight too many cells the little button does not appear. In the below screen grab it IS appearing, but I find if I select more than say 2000 cells the option doesn't appear.
    Click image for larger version. 

Name:	Microsoft Excel - SewerMainsConq.xlsm_2014-02-11_09-09-48.jpg 
Views:	4 
Size:	31.4 KB 
ID:	2043


    What I have found is a great bit of code which works well, but it is VERY slow.. it basically goes cell by cell converting one by one. Probably about 3 cells per second, and some workbooks have about 50,000 cells to be converted, so I am looking for a better piece of code to do this. The code I am using at the moment is pasted below.

    Sub Convert2Number()


    For Each xCell In Selection


    Selection.NumberFormat = "0" 'Note: The "0.000" determines the number of decimal places.


    xCell.Value = xCell.Value


    Next xCell


    End Sub



    One thing to note is this PC is less than 12 months old and is very powerful as it runs our GIS/Mapping/Cad software with ease.

    Can anyone help me speed my code up?

    Thanks very much.
    -Cheers

  2. #2
    Ok to answer my own question, I think I have found a much quicker (and I literally mean it takes < 1 second to complete) solution...

    It seems to work a treat!

    Code:
    Sub ConvertSelectedText2Numbers()
     'Convert all cells with numbers from text to numbers
        Dim rUsedRange As Range
        For Each rUsedRange In Intersect(ActiveSheet.UsedRange, Selection).Areas
        rUsedRange.Value = rUsedRange.Value
        Next rUsedRange
    End Sub
    Thanks anyway, I hope this thread can help someone in the future! Goodluck future person!

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,267
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Thanks for posting back!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    Did you try ?

    Code:
    Sub M_snb()
      usedrange.value=usedrange.value 
    End Sub

  5. #5
    Quote Originally Posted by snb View Post
    Did you try ?

    Code:
    Sub M_snb()
      usedrange.value=usedrange.value 
    End Sub
    Are you able to elaborate on how this one works? Do I simply run this small macro and it searches the entire document for fields that can be converted from text to number? Do I have to have them selected?? Etc

    The reason I ask is that the current code needs to have a selection before running I believe. Is the code you provided a step up from this?

    Code:
    Sub ConvertSelectedText2Numbers() 'Convert all cells with numbers from text to numbers
        Dim rUsedRange As Range
        For Each rUsedRange In Intersect(ActiveSheet.UsedRange, Selection).Areas
        rUsedRange.Value = rUsedRange.Value
        Next rUsedRange
    End Sub
    Thanks, as you can probably tell I am a macro n00b at this stage, trying to find my way!

    Cheers

Tags for this Thread

Posting Permissions

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