Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Numbers Refusing Text Format (9.0.6926 SP-3)

    I receive a list of codes every month from 4 separate sources. The codes (cells) are formatted as general. I combine all codes in one spreadsheet and format the "code" column as text. The cell format says that it is text, but unless I go into edit mode (by either double clicking the cell or F2) and hit enter in each individual cell, the value doesn't change to text.

    I need to import the spreadsheet into MS Access to bounce against another list of the same codes. If I import into MS Access without editing each cell, the design view of the table says the column is text, but when I match on the other table (existing list of codes), I don't receive any matches.

    How can I convert the entire column to text, without editing each cell individually.

    Thank you,

    Stearvi

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

    Re: Numbers Refusing Text Format (9.0.6926 SP-3)

    - select the cells
    - data, text to columns
    - delimited
    - next, next
    - now specify column format to be text.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers Refusing Text Format (9.0.6926 SP-3)

    I have tried that.....but the issue is that the codes are 7 digits long and many have 1, 2, 3 or more preceeding zeroes. The zeroes end up truncating.

    Thank you!

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers Refusing Text Format (9.0.6926 SP-3)

    See if running this VBA code on the data before you import it to Access makes any difference: <pre>Sub ConvertToText()
    Dim d, lLastRow As Long
    lLastRow = Cells(Application.Rows.Count, Columns("A").Column).End(xlUp).Row
    For d = 1 To lLastRow + 1 Step 1
    ActiveCell.Value = "'" & ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    Next d
    End Sub</pre>

    HTH
    Gre

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers Refusing Text Format (9.0.6926 SP-3)

    Can you be a little more specific about what you are doing?

    How are these codes coming to you? In an Excel spreadsheet? If so, what is the format of the source cells? If it is not Text, what is it?

    How are you combining the codes into one sheet? Copy/Paste? Macro? Are you formatting the cells on the combined sheet before moving the codes there?

    Changing the format of a cell that contains a number will not convert the number to text.

    If I copy cells containing text numbers and paste them into cells that were previously formatted as text, I get text.
    Legare Coleman

Posting Permissions

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