+ Reply to Thread
Results 1 to 6 of 6

Forcing numbers stored as text back to numbers

  1. #1
    Forum Contributor
    Join Date
    01-11-2011
    Location
    Frederick, MD, USA
    MS-Off Ver
    Excel 2007
    Posts
    125

    Forcing numbers stored as text back to numbers

    I've got a whole plethora of worksheets that have COUNT based formulae, operating on a given subset of data that is copied-and-pasted into these worksheets. Unfortunately, an "upgrade" to the system from which that data is copied has changed the primary datapiece to be changed from a number to a number-stored-as-text when I copy stuff in. I'd like the users of my sheets to not have to do the select-and-convert thing every time the use one of my worksheets.

    At first, I thought I could get around it by using COUNTA instead of COUNT, but that won't work; I use COUNT to determine when, in a set of numbers, to insert some text, so I really need a way to differentiate thes numbers-stored-as-text from actual text.

    Is there some way to force pasted data back to numbers? Or at least treat them as numbers is other places in the worksheet?
    Last edited by Gunther Maplethorpe; 01-20-2011 at 01:17 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Forcing numbers stored as text back to numbers

    COUNTA counts everything.
    Alternately, if you might have real text that you don't want to count, you could
    =COUNT(VALUE(Range)) as an array (use CNTRL SHFT ENTER instead of Enter)
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Forcing numbers stored as text back to numbers

    Is there some way to force pasted data back to numbers?
    Select the columns in turn, Data > Text to columns, Finish
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    10-05-2010
    Location
    S.A
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: Forcing numbers stored as text back to numbers

    hi
    there is one other way, when you paste your data
    you right click, then paste special
    in operation select Add
    by this way, it will paste text numbers as numbers
    thanks

  5. #5
    Forum Contributor
    Join Date
    01-11-2011
    Location
    Frederick, MD, USA
    MS-Off Ver
    Excel 2007
    Posts
    125

    Re: Forcing numbers stored as text back to numbers

    Paste Special doesn't work for some reason; the only options I get are past as Picture, Bitmap, Excel 8.0, SYLK, DIF, Unicode, or Text...and none of them seem to paste as numbers. Not sure by what you mean when you say "in operation"; perhaps that's a different version of Excel (I'm using version 12.0)?

    Adding the VALUE( operator didn't work directly, but I added a 'helper' column on the front page that converts, and then I can call that column on subsequent pages...this will be a simple fix, and easy to send out across all my templates, without having to re-train my users. Thanks very much for that insight!

  6. #6
    Forum Contributor
    Join Date
    01-11-2011
    Location
    Frederick, MD, USA
    MS-Off Ver
    Excel 2007
    Posts
    125

    Re: Forcing numbers stored as text back to numbers

    Quote Originally Posted by shg View Post
    Select the columns in turn, Data > Text to columns, Finish
    Quote Originally Posted by Gunther Maplethorpe View Post
    ...I'd like the users of my sheets to not have to do the select-and-convert thing every time the use one of my worksheets...
    The VALUE operation, in a helper column, as listed above, will work - unless anyone has something more elegant?

+ 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