Maybe something like this to reconvert dates from 3-May to "3-5"

Sub test()
Dim s as String
Dim c As Range

For Each c In Range("A1:A10")
If IsDate(c) Then
With c
s = Day(.Value) & "-" & Month(.Value)
.NumberFormat = "@"
.Value = s
End With
End If
Next

End Sub

It might be worth including one or two more If checks before changing
anything, eg

If instr(1, c.Text, "-") then
if instr(1, c.numberformat, "d") then

Instead of changing the numberformat to text, maybe change it to general and
prefix the new string with an apostrophe.

Regards,
Peter T


wrote in message
...
When using Excel for mail merging at work, when we get an address like
"3-5 East Street" and "3-5" is in a cell on its own, separate from
East Street, Excel will change the "3-5" to 3-May (U.K. date format).
This is because it is trying (unsuccessfully) to ascertain the format
of the data in the cell.

We can get round this by changing the cell format to "Text", but then
it will change to something like "39936", Excel's way of storing
dates. Either way, it doesn't do what we want!

Usually, if we notice a cell that has changed to "3 May", it is easy
to figure out what it should have been, and change it manually. This
is not practical on a spreadsheet of 20,000 addresses!

I thought a VBA solution might be possible, if the macro could go
through each cell in the ActiveRange, seeing if it is formatted as
"Date". If it is, the macro could halt with the ActiveCell as that
cell. Then we could locate and change these problem cells quickly.

It seems the property I need is "ActiveCell.NumberFormat", but I am
having trouble getting this to work in a macro. Plus,
ActiveCell.NumberFormat does not return something like "Date", it
returns the date format of that cell. I am also having trouble
getting the macro to stop dead when it discovers a date, and making
the problem cell the ActiveCell, so I can see where it is.

Does anyone have any suggestions as to how I might overcome this
problem? Is VBA the right way to solve it?

Steve