+ Reply to Thread
Results 1 to 6 of 6

Mass editing of turning text to numbers

  1. #1
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Mass editing of turning text to numbers

    Hi

    I'm trying to convert some data which excel is reading as text into numbers for example I have:

    Please Login or Register  to view this content.
    All the numbers I've imported from an app analytics company into excel which are greater than 1.0 appear as numbers (to the right side of the cell) and all the numbers less than 1.0 appear as text (to the left side of the cell). I want all the numbers appearing as text to appear as numbers so I can multiply them with other cells.

    I've clicked format cells - number but this has no effect. I click format cells -protection - unlocked but this has no effect.

    I have thousands of rows with all numbers less than 1.0 as text instead of numbers. What's the easiest way to mass convert them all to numbers seeing as the above options haven't worked.

    I don't want to have to click through thousands of rows to edit it manually...

    Any help appreciated!

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Mass editing of turning text to numbers

    Is your decimal separator a dot? If so, using the values in multiplications will coerce them automatically to numbers ( in so far there are no hidden spaces or anything)

  3. #3
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Mass editing of turning text to numbers

    yes they are dots, but because Excel reads them as text when I enter the formula =ISTEXT is gives a true value which then gives a #VALUE error when I multiply it by another column.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Mass editing of turning text to numbers

    Post a sample sheet please

  5. #5
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Mass editing of turning text to numbers

    Hi no.18shirt

    Have tried selecting a blank cell & copy, select the column your data is in, Paste Special, Add.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Mass editing of turning text to numbers

    Quote Originally Posted by Kevin UK View Post
    Hi no.18shirt

    Have tried selecting a blank cell & copy, select the column your data is in, Paste Special, Add.
    Probably won't work, it is also based on coercion through arithmetical operators

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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