+ Reply to Thread
Results 1 to 5 of 5

Convert numbers stored as text to numbers for range of data that changes

  1. #1
    Forum Contributor
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    146

    Convert numbers stored as text to numbers for range of data that changes

    I have about 5000 rows of data and column G has variations in numbers and letters. Some have purely 7 numbers others have numbers and letters in random orders. The data (pure numbers) comes to excel stored as text and it would be extremely tedious to go through and convert each to the number format. I am wondering what an easy way to do this because I tried going to format cell>number and it still comes with the error to change to number. I later want to make a macro if possible to simplify this process so if thats possible that would be great otherwise maybe just a formula? I uploaded a sample workbook Book1.xlsx

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

    Re: Convert numbers stored as text to numbers for range of data that changes

    Select column G (if you have multiple columns, must do 1 column at a time) go to Data tab of ribbon> "Columns to text" then just hit "Finish"
    Does that convert all your number text to numbers?
    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 bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,289

    Re: Convert numbers stored as text to numbers for range of data that changes

    Try one of these in helper column:
    =G1-0
    =G1+0
    =G1*1
    --G1
    Then copy/paste value back to G1
    Quang PT

  4. #4
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Convert numbers stored as text to numbers for range of data that changes

    Hi, try copy column G data to notepad.
    Then copy back the data from notepad to column G.


    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

  5. #5
    Forum Contributor
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    146

    Re: Convert numbers stored as text to numbers for range of data that changes

    That seems to have done it, I also went to format cell and changed back to general. Thanks!

+ 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. [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
  2. [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
  3. Replies: 2
    Last Post: 10-22-2009, 02:50 PM
  4. [SOLVED] 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
  5. [SOLVED] How do I convert numbers stored as text with spaces to numbers
    By Baffuor in forum Excel General
    Replies: 1
    Last Post: 05-24-2005, 06:32 AM

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