VBA: Preventing numbers automatically changing to dates ?

[JumboCactuar]

Hi,
this only happens with numbers that look like a date:

I have data like this:
1 to 9
10 to 12
13 to 15
17 to 19

Now i need it to look like
1 - 9
10 - 12
13 - 15
17 - 19

Problem is with the first two, get converted to01-Sep and 10-Dec

this is what ive tried:

Range("A2:A1000").NumberFormat = "@"
frm1 = "to"
to1 = "-"
Columns("A").Replace what:=frm1, replacement:=to1, lookat:=xlPart, MatchCase:=False

any help appreciated

 

[kweaver]

There might be a more clever VBA way, but this worked for me (still relatively new using VBA):

Sub FixDate()
ActiveSheet.Select
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & LR).Formula = "=Left(A2, Find("" "", A2)) & "" - "" & Right(A2, Len(A2) - (Find(""to"", A2) + 2))"
Range("A:A").NumberFormat = ";;;@"
Range("A2:A" & LR) = Range("B2:B" & LR).Value
Range("B:B").EntireColumn.Delete
End Sub

 

[JumboCactuar]

There might be a more clever VBA way, but this worked for me (still relatively new using VBA):

Sub FixDate()
ActiveSheet.Select
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & LR).Formula = "=Left(A2, Find("" "", A2)) & "" - "" & Right(A2, Len(A2) - (Find(""to"", A2) + 2))"
Range("A:A").NumberFormat = ";;;@"
Range("A2:A" & LR) = Range("B2:B" & LR).Value
Range("B:B").EntireColumn.Delete
End Sub

Thankyou though this didn't work as expected and formatted some of my numbers as blank

I forgot to mention my original data is also mixed with correctly formatted data so that could be why

Example:
1 to 8
9
10 - 12
13 to 14
15
16-17

I need it to be:
1 - 8
9
10 - 12
13 - 14
15
16 - 17

 

[Fluff]

Another option, rather than using a dash -, you could use an En dash –

Range("A2:A1000").NumberFormat = "@"
frm1 = "to"
to1 = Chr(150)
Columns("A").Replace what:=frm1, replacement:=to1, lookat:=xlPart, MatchCase:=False

 

[JumboCactuar]

Another option, rather than using a dash -, you could use an En dash –

Range("A2:A1000").NumberFormat = "@"
frm1 = "to"
to1 = Chr(150)
Columns("A").Replace what:=frm1, replacement:=to1, lookat:=xlPart, MatchCase:=False

Thankyou I will try this