+ Reply to Thread
Results 1 to 6 of 6

Unable to convert numbers that think they are text into something that can be counted

  1. #1
    Registered User
    Join Date
    03-12-2004
    Location
    Harpenden
    Posts
    18

    Unable to convert numbers that think they are text into something that can be counted

    I have a series of numbers that although they look like numbers and are formatted as numbers are not numbers they are something else. I have tried text to columns but something weird is occurring so the question is as follows: can someone work out a simple formula that will count text as a number and return the highest and the lowest number.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Unable to convert numbers that think they are text into something that can be counted

    see attached.

    Convert text to number by simply multiplying by 1.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Unable to convert numbers that think they are text into something that can be counted

    Hi Aajt8888,

    The "numbers" in Cols Q:T are formulas (=RIGHT(A2,7) copied right and down.
    Highlight (select) all the cells, copy, paste special (over the original cells) and select Values.
    When the green triangle appears click and select "convert to number".
    The formulas you already have will then work.

    Regards

    peterrc

  4. #4
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Unable to convert numbers that think they are text into something that can be counted

    attachment.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Unable to convert numbers that think they are text into something that can be counted

    Just change the formula to
    =RIGHT(A2,7)+0
    Then fill down & across

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Unable to convert numbers that think they are text into something that can be counted

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. VBA to convert a range from numbers to numbers stored as text (Text to Columns)
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-06-2017, 05:50 PM
  2. Unable to convert data to text to column
    By ruchika137 in forum Excel General
    Replies: 2
    Last Post: 09-30-2015, 11:25 AM
  3. [SOLVED] Convert all numbers stored as text or custom formatted to numbers &no decimals - 40 sheets
    By synses in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:46 AM
  4. [SOLVED] How do you apply the int() in VBA in order to convert numbers stored as text to numbers
    By djaurit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2012, 02:57 PM
  5. Replies: 2
    Last Post: 10-22-2009, 02:50 PM
  6. Duplicate numbers counted
    By sleepy in forum Excel General
    Replies: 1
    Last Post: 07-17-2009, 07:49 AM
  7. Convert numbers stored as text to numbers Excel 2000
    By Darlene in forum Excel General
    Replies: 6
    Last Post: 01-31-2006, 04:10 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