don't autoformat, treat date as text

[shantanuo]

Hi,
Is there any option, if selected will treat all dates as plain text?
I know I can use ' (Single quot) to treat it as text.
My problem is that I don't remember to use it while I am typing.
Is there any option in tools?

Shantanu Oak

 

[Aladin Akyurek]

Format the cells as text before any entry.

 

[shantanuo]

Macro/ function?

Is there any macro/ function that I can run once I have typed all the data?

 

[Joe4]

It can be done quite easily.

Highlight the column with the dates. From the Data drop down menu, select Text To Columns. This works very much like the Import Wizard. Click Next twice until you come to the Column Data Format section. Select the "Text" radio button and click Finish. Your dates should now be converted to strings (text).

 

[shantanuo]

Hi,
Thanks. But Only a part of my problem solved.
This tip can be used only for a single column.
What about if I want to update the entire sheet?

Shantanu Oak

 

[Joe4]

If you have just a few columns, it should be easy enough to do each manually. If you have numerous colulmns, here is some code I wrote to repeat the Text to Columns for all columns that have data in Row 1:

Sub FixAllColumns()

    Application.ScreenUpdating = False
    
    Dim MyLastColumnAddress As String
    Dim i As Integer
    
    MyLastColumnAddress = Range("IV1").End(xlToLeft).Address
    
    Range("A1").Select
    Do
        On Error Resume Next
        i = i + 1
        Cells(1, i).Select
        Range(Cells(1, i), Cells(65536, i)).Select
        Selection.TextToColumns Destination:=Cells(1, i), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 2)
       If ActiveCell.Address = MyLastColumnAddress Then Exit Do
    Loop
    
    On Error GoTo 0
    
    Application.ScreenUpdating = True

End Sub

P.S. I edited the code to account for any blank columns.