Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing a number field to a text (Excel 2000)

    Does anyone know how to change a number column to a text column. I am doing this - formatting the column as text and then each field I am pressing F2 and entering. Does anyone know a faster way? The fields in the number columns have to be text because I am importing the spreadsheet to an Access database and the fields in the access database are text. Any help would be appreciated. Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing a number field to a text (Excel 2000)

    Add a new column
    Enter in that columnassuming that the numbers are in "A1 to whatever"[Change the format code if desired]
    =TEXT(A10,"0.0")
    Copy this down the column
    Copy and Paste-special values over the numbers in Col A
    Delete the new column
    Steve

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

    Re: Changing a number field to a text (Excel 2000)

    <P ID="edit" class=small>(Edited by gwhitfield on 17-Sep-02 06:52. Hyperlinks added)</P>You might want to consider using ASAP Utilities, a free download from:

    http://asap-utilities.com

    The option to use is: Numbers|Convert numbers to text (adding ' in front)

    Aladin
    Microsoft MVP - Excel

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Changing a number field to a text (Excel 2000)

    Another solution, using a macro:

    Option Explicit
    Sub Convertvalues()
    Dim CellContent As Variant, NewData As Variant
    For Each CellContent In ActiveSheet.UsedRange
    If CellContent.HasFormula = False Then ' leave cells with formulae alone
    If Len(CellContent) > 0 Then
    NewData = CellContent
    CellContent.FormulaR1C1 = "'" & NewData
    End If
    End If
    Next
    End Sub


    This macro goes through the whole worksheet and changes all numbers (but not formulae) to text strings. If you only want to do this for a selected range, change the 'ActiveSheet.UsedRange' parameter to 'Selection'.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Changing a number field to a text (Excel 2000)

    How are you doing the import? Access should be capable of converting the fields to text as part of the import process.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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