Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Text Numbers (Office 97)

    Help

    I have a spreadsheet with a column of codes that have been entered as text not 10 as ten but 10 as text.

    I need to convert all these to numbers to number format to compare them with a similar spreadsheet where the numbers are entered as numbers. Hope this is clear.

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    If you are a fool at forty, you will always be a fool

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Numbers (Office 97)

    Quickest way is to format cells as numbers, then 'refresh' them _individually_ with F2+Enter

    If you have a lot of these cells , then the safest way is to make a macro for this operation

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Numbers (Office 97)

    ( 1.) Select an unused, empty cell.
    ( 2.) Activate Edit|Copy.
    ( 3.) Select the range of interes, that is, the number range with text as underlying format.
    ( 4.) Activate Edit|Paste Special >Add.

    You're done.
    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Text Numbers (Office 97)

    Thanks for help

    But there far to many to do individualy, I have a feeling that someone offered an alternative like placing 1 in a cell and copy and paste special then multiply.

    But I have lost the instruction.

    Thanks
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Text Numbers (Office 97)

    Hi Aladin

    I tried what you said but when I paste special add the cells are blank, perhaps I do not understand you correctly.
    If you are a fool at forty, you will always be a fool

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Numbers (Office 97)

    <<but I have lost the instruction>>

    Well, you just wrote it yourself!!

    - 1 in an empty cell
    - copy
    - select text cells
    - Edit, Paste-special, select the multiply option, OK.

    Alternative:

    - select offending cells (one column at a time)
    - Data, text-to-columns, fixed width option, Finish.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Text Numbers (Office 97)

    Thanks again to everyone who responded to this

    Much gratitude
    If you are a fool at forty, you will always be a fool

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Numbers (Office 97)

    You haven't lost those numbers, I hope.

    Let A2:A40 house text-formatted numbers.

    Let D1 an empty, unused cell.

    Click in D1. Activate the option Copy under Edit.

    Select A2:A40. Choose Edit. Choose Paste Special. Check Add as Operation.

    That should do it.

    Aladin
    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Text Numbers (Office 97)

    Thanks

    I understand now and it worked fine

    Thanks again
    If you are a fool at forty, you will always be a fool

Posting Permissions

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