It’s turning my numbers into dates (XP)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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:

Sub change()
    Dim cCell As Range
    For Each cCell In Selection
        cCell.Value = Replace(cCell, ".", "")
        cCell.NumberFormat = "@"
    Next
End Sub

Reply | Quote

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 ~ )

Reply | Quote

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.

Reply | Quote

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

Reply | Quote