CODEWITHSUNDEEP
excelexcel-formulaformatting
Shawn Mogensen
Shawn Mogensen

Reputation: 51

How to make excel stop treating column as date

My column values are as follows:

1-1
1-2
2-1
2-2
...
12-1
12-2
13-1
13-2
...
n-1
n-2

On cells containing 1-1 up to 12-2, excel doesn't meddle with me, but starting on 13-1 up to the nth row, it shows warning "Text date with 2-digit year". Unfortunately, this are really not dates. Also, I already tried formatting the said rows/cells as text before inputting any values, but really when excel sees "13-1" and above, it shows the error. How should I fix this?

Additional info: when the rows are shown using formula, there are no errors, but sadly, using formula is not an option and I need values as is.

Upvotes: 3

Views: 2166

Answers (5)

jeppoo1
jeppoo1

Reputation: 698

Stop excel from converting copy-pasted number/text values to date

  1. Copy the original data
  2. Paste to Notepad / Notepad++
  3. In Excel, change the Format Cells / Number Format (that shows "General" as default) to TEXT
  4. Copy your data from Notepad
  5. Paste back to the Excel cells, in which you have changed the format to TEXT

Upvotes: 1

LJ01
LJ01

Reputation: 589

If you add a single quote to the start of the dates Excel treats it as a string. Works for numbers as text too

Upvotes: 1

Uttam Manher
Uttam Manher

Reputation: 152

You can type ="1-1" instead of just 1-1 When you type 1-1, excel change this to 1-Jan. When you type ="1-1" , Excel treat the value as string. Hope it helps.

Upvotes: 5

user4039065
user4039065

Reputation:

Turn off error checks for those types of errors.

With Application.ErrorCheckingOptions
    .TextDate = False
    .NumberAsText = False
End With

Also found within File, Options, Formulas, Error checking rules.

Upvotes: 0

Tarek.Eladly
Tarek.Eladly

Reputation: 759

Just right click on the column click on Format Cells ... on Number Tab on Category List select Text, this will make anything you enter or have as a text.

But if you are copy paste data then use Match Destination Format (M), just right click on the cell and select Match Destination Format (M) not Ctrl-V.

Upvotes: 3

Related Questions