As we all know, Excel is a pretty intelligent program. It often auto identifies the type of data that we want to put in. As this can be useful in many cases, it can prove to be a real drag as well. In this article, we’re going to address an issue exactly like that.
I mean, putting in some numbers and seeing them turn to dates isn’t something we always want. So, if you’re wondering how to stop Excel from changing numbers to dates, you’ve just hit the jackpot. In this one, we’ll be discussing the procedures in detail.
So, if you want to solve the issue, I’d suggest you to go through the whole thing bit by bit. I bet you won’t be disappointed.
Contents
The Reason Excel Changes the Numbers to Date
As we’ve said before, Excel is a very sophisticated and intelligent software. So, when you see Excel turning a set of numbers to dates, there is a valid reason. Let’s discuss:
- Well, first of all, excel does this because it’s trying to help, the integrated program wants to give you a hassle-free experience of putting date and time in the proper format.
- This doesn’t necessarily mean that Excel is changing all numbers that can merely represent a date. It changes the underlying cell value as well.
Ways to Stop Excel from Changing Numbers to Dates
Now that we’re done with the introductory stuff, let’s dig into the main dish. The best way to go about it is, clearly letting Excel understand that the strings or numbers that are being put in are not dates. Wondering how? Let’s find out.
Using Apostrophe
This has got to be one of the easiest ways to stop Excel from turning numbers into dates. Here’s how it works.
- The first thing you need to do here is, put an apostrophe before starting to type in the number.
- This makes Excel think that you’re typing in a text.
- Here, the apostrophe sign appears only in the formula bar and not on the cell itself.
- Here, we’ve put the data in the center, but you’ll generally see the numbers move left if apostrophe is applied.
- The difference between with and without apostrophe is clear in the image above.
Note: A certain benefit of putting apostrophe in the beginning is that, the VLOOKUP or MATCH can be used to find the data in the cells. The apostrophe will be ignored in this case.
Changing the Cell Format
This is perhaps the most practical approach. We’re basically making Excel acknowledge that the data we’re putting is not in date format. Let’s see how we can pull this off.
- The first thing you’ll want to do is, select the range of cells that you want to change the format of.
- Then, under the home tab, select the dialog box launcher button under the Home tab’s Number group.
- Under the Format Cells Dialog box, select the Text format and click OK.
- Now, within the selected range, the numbers that you put in won’t turn into dates.
Note: Do this procedure before you put in the data. Doing it after you’ve put in the numbers will result in the cells showing the numeric value.
Adding Space
The two methods that we discussed are the most common and basic procedures to get the job done. However, if you require more options, you can always add space before the numbers. Let’s see how.
- Closely look at the image given below. Adding space in the beginning prevents ¾ turning into the third of April.
- The space still shows in the formula bar.
- That’s pretty much it. The procedure is as simple as that.
Adding Zero and Space
This is pretty similar to adding space in the beginning. Let’s see how it’s executed.
- Firstly, we’ll have to add zero and space before ¾ to ensure it doesn’t turn into the third of April.
- Once we hit enter, the zero will disappear in the cell, but the decimal value will be shown in the formula bar.
- See? It works like a charm.
Using VBA Code
For those who use Microsoft Visual Basics, it’s pretty simple to get the desired results by putting in a particular code. Here’s how to do it.
- Firstly, press Alt+F11 to bring up the visual basic editor.
- Here you’ll want to go to Module, and select insert from the dropdown menu.
- Just paste the code that’s shown below:
Sub Stop_Change()
Dim d As Date
d = Date
With Range(“D5:D10”)
.NumberFormat = “@”
.Value = Format(d, ” “)
End With
End Sub
- Now, run the code.
- You can just press F5 to do it as well.
- And voila! The numbers with “-“ and “/” won’t change to dates anymore.
Final Words
If you don’t use VBA codes frequently in your workspace, the first method can be the fastest way for you to complete the task. However, if there are multiple cells to work with at the same time, the format cells method is best suited.
Now that we’ve completed the article, I hope all the info helped you out. These methods are really handy and can definitely help you out in your regular excel ventures. Let’s wrap things up for now. Farewell.
Similar Post: