Excel Wizards Again! Faster way to "reset" a field than select/enter?

Factual Questions
#1

What do I mean?

Well, let’s say a column of dates that has been imported from another program is being seen and read as text in Excel. I can change the format and/or copy/paste values only…but the only way that Excel comes to KNOW that these are now dates in a date-formatted cell is if I click on the cel to select it (only clicking works, not keyboard navigating to it.) then hit enter or return…poof! The cell suddenly understands that the contents are a date.

This is incredibly irritating and stupid and time-consuming and decidedly low-tech.

There must be a better way.

Help!

#2

You can select the whole column and apply cell formats to it to let Excel know that all of them should be treated as dates.

You can also use the List Wizard for the same function (though I despise the LW).

#3

Ya know, if you spent $100 on a beginning Excel class …

www.newhorizons.com
among many others

#4

sidenote: i’m pretty sure you can navigate with your keyboard and once at the desired cell press F2, then Enter.

#5

I made this macro for that problem, importing copied data from a Chinese system:

Sub Force_chinese_to_numbers()
Dim sttemp As String
Do While Selection.Text <> “”
Selection.Offset(1, 0).Select
sttemp = Selection.Text
Selection.Value = sttemp
Loop
End Sub
It will run down a column from whereever you have selected, clicking into each cell below to reset the default formatting.

#6

I know the problem you’re talking about, and it really irritates me. I’d love it if Excel would just understand that when I say “Format as date” it would know I mean “including what’s entered as text.” Here’s my best solution:

Let’s say your copy-and-pasted text is Column A. Insert a blank column somewhere (B, in our example) and format it with the date format you want.

In B1, enter “VALUE(A1)” and drag the formula down as needed to convert the rest of your numbers.

Copy and paste special>values to get rid of the formulas.

It’s still tedious, but not as bad as having to hit return in each cell.

#7

I don’t know why you can’t just format the column unless there is a blank space but you could try “text to columns” and use the date function (but don’t actually create any additional columns).

Could you post an example that we can import to test?