Convert 1-1 back to 1-1 from being converted to a date

[ChrisMcIntyre]

Morning all, Happy Friday!!

A system I use spits out a document directly into Excel, but frustratingly some of the columns have numbers like 1-1 (one of one) or 3-9 (three of nine), and Excel is converting these numbers to dates.

Is there an easy way to convert these back to the original system numbers?

If there's no simple method of conversion that doesn't involve lots of retyping then, I would love to build something in VBA whereby the user clicks a button on a sheet, it prompts the user to choose the file to format and then it does all the formatting and then prompts to save.

Bottom line, I want the quickest solution possible please... And I know someone out there knows the pesky solution

 

[ChrisMcIntyre]

WORKS PERFECTLY!!!

Thanks so much Peter, really appreciate that... Made even more special as it's Friday!! YAY!!!!

 

[ChrisMcIntyre]

Worked an absolute dream.

You're going to hate me, and I apologise for the back and forth, but is there anyway for the VBA to ask which sheet to format? I just realised that the sheet in the data is not always called "Sheet1"

Is that a big ask?

 

[Peter_SSs]

is there anyway for the VBA to ask which sheet to format?

Before going down that path: Is there anything about the sheet's name (eg always includes particular text or number layout as part of its name), position (eg left most sheet) or something in the worksheet (eg a particular heading or number of columns etc) that might allow the code to itself determine the correct sheet to use?

 

[ChrisMcIntyre]

Unfortunately not. Ideally one would need to be able to input the sheet name, something I’ve not done before. For now, I am revising the sheet to Sheet 1 and then changing it back once the macro has run, it works, so no biggie if this is a big task. I am happy to stay as is, but if you want to demonstrate your skills and you’re bored one day, I’d live to know if this is even possible.

Thanks Peter.

 

[Peter_SSs]

Try this

Sub ConvertBack_v3()
  Dim sFile As String, sName As String
  Dim wbData As Workbook
  Dim wsData As Worksheet
  
  sFile = Application.GetOpenFilename()
  If sFile <> "False" Then
    Set wbData = Workbooks.Open(sFile)
    sName = InputBox(Prompt:="Type sheet name to process")
    On Error Resume Next
    Set wsData = wbData.Sheets(sName)
    On Error GoTo 0
    If wsData Is Nothing Then
      MsgBox "Unable to locate sheet " & sName
    Else
      With wsData
        .Activate
        With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
          .NumberFormat = "@"
          .Value = Evaluate(Replace("if(#="""","""",text(#,""m-d""))", "#", .Address))
        End With
      End With
    wbData.Save
    End If
  End If
End Sub

 

[ChrisMcIntyre]

Thanks Peter, I will take a look and test this when I am back in the office in a week’s time…. Thanks so much for working on something over the weekend!

 

[Peter_SSs]

You're welcome. For me it is a hobby, not work, so weekend is as good as any other day. ?

 

[ChrisMcIntyre]

Hi Peter, just to let you know that the new code worked an absolute charm, thanks so much!
All the best!
Chris

 

[Peter_SSs]

Glad it worked for you. Thanks for the confirmation.