numbers contain hyphens to dates

[Guest]

I have copied and pasted a file from a website into Excel 2000,in column "B"
there was a entry like this:-
11/14 which means 11th place from 14 positions. Excel shows this entry as
Nov-14 (a date). I have tried
every thing that I could find , but can not bring it back to 11/14 (which is
very important for my end result)
There are about 300 rows that I have to import every day.
Is there a worksheet function that you know of ?
Or is there a macro that would work?
Can you please help ?

hopeful Bill

 

[Norman Jones]

Hi Bill,

To convert the spurious dates to fractional numbers, try::

Public Sub DatesToFractions()
Dim RCell As Range

For Each RCell In Selection '<<===== CHANGE
With RCell
If IsDate(.Value) Then
.Value = 0 & " " & Month(.Value) _
& "/" & Day(.Value)
End If
End With
Next

End Sub

 

[Norman Jones]

Hi Bill,

Or to convert the spurious dates to text fractions, try:

'=======================>>
Public Sub DatesToTextFractions()
Dim rCell As Range
Dim rng As Range

For Each rCell In Selection
With rCell
If IsDate(.Value) Then
.NumberFormat = "@"
.Value = Month(.Value) _
& "/" & Day(.Value)
End If
End With
Next

End Sub
'<<=======================

And, if the values to be converted were always in a specific column (say
column B) then, try:

'=======================>>
Public Sub DatesToTextFractions2()
Dim rCell As Range
Dim rng As Range
Const myColumn As String = "B" '<<==== CHANGE

With ActiveSheet
Set rng = Intersect(.UsedRange, _
Columns(myColumn))
End With

For Each rCell In rng
With rCell
If IsDate(.Value) Then
.NumberFormat = "@"
.Value = Month(.Value) _
& "/" & Day(.Value)
End If
End With
Next

End Sub
'<<=======================

 

[Piranha]

Bill,
If you just want a key entry way of doing it.
Enter a zero and space before the fraction.
(0 7/8) will show as 7/8

Dave

 

[Norman Jones]

Hi Piranha,

---
Regards,
Norman

Bill,
If you just want a key entry way of doing it.
Enter a zero and space before the fraction.
(0 7/8) will show as 7/8

Dave

 

[Piranha]

Norman,
See thats why you are the brain, i missed that

P.S. Norman, may i email you about that workbook you fixed?
I still have your address, if its Ok.
Dave

 

[Norman Jones]

Hi Dave,

That would be fine!

 

[Guest]

Thank you very much for your time and input