Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    It's turning my numbers into dates (XP)

    I have some labels in the style 1/1, 1/1a, 1/1b, 1/2 etc.

    The data is imported in from Access. I have to add a period/full stop to the labels which don't contain a letter (e.g. 1/1.) otherwise they are interpreted as dates.

    With the data in Excel, if I simply delete the period/full stop the labels appear fine. If I do a find and replace it turns them into dates.

    Any suggestions.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It's turning my numbers into dates (XP)

    If you are putting the periods in front of the labels, you can replace the periods with an apostrophe, which will not get displayed if it is the first character in the cell.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: It's turning my numbers into dates (XP)

    i also had this problem but with a - sign, I access I simply converted via search and replace in the field the minus sign - to a non date separator, like | or . I hope this helps. but i will now use Mike's solution

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It's turning my numbers into dates (XP)

    Mike

    I'm sure I tried that but could see the apostrophe.

    (Edit - just tried again and I can't see it - so far so good)

    But, the data will be used in a rather mail merge - would the apostrophe be carried over into word?

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It's turning my numbers into dates (XP)

    I don;t know what would happen in the mail merge

    If you don't mind using a macro, the following will rid the cells of the periods and format the cell as text. Just highlight the region you want to rid of periods and run the following:
    <pre>Sub change()
    Dim cCell As Range
    For Each cCell In Selection
    cCell.Value = Replace(cCell, ".", "")
    cCell.NumberFormat = "@"
    Next
    End Sub
    </pre>


  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It's turning my numbers into dates (XP)

    Might just let them have it (with invisible apostrophes) and see what happens.

    The trouble is, the overall range that the cells occur in have perfectly valid periods.

    I guess I could copy over an character unlikely to be used in other bits of text (say ~ )

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: It's turning my numbers into dates (XP)

    The apostrophes won't carry over into the merged document. They are just used by Excel to indicate that the cell value is text even if it looks like a number or a date.

Posting Permissions

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