Cannot reformat cells

[Suzanne53]

I have imported data and evidential something is wrong with the data in each column because I cannot reformat it to a date format that I need. I need it to be 2013-01-01. (all years will be 2013). I have the month and day in the cell already, but there MUST be an extra space or something that I can't see because it won't convert. If I go to replace and tell it to replace all 5/01 (COPIED directly from the original cell) to 5/01 TYPED in by me, then it works. I have gone to each cell and put my cursor in the cell at the end of the data and hit "TAB" and it works! I have tried converting the entire column to TEXT or GENERAL to try to get anything 'strange' out of there and it won't work. The only thing that really works is putting the cursor in each cell and tabbing out. Can anyone see what is wrong with the imported information? This comes from external data that we have set up to pull from 2 different databases - the other one has over 9000 entries. So I need to figure out why the data is coming in with something that is keeping it from converting. Thanks in advance...

[JieJenn]

In C3, enter =VALUE("2013/"&A3) and drag across and down

[Jonmo1]

Copy any Empty Cell
Highlight the range of 'dates'
Right Click - Paste Special - Values - Add - OK

[Suzanne53]

I have copied the two columns from a spreadsheet so I can't add a column.

[Suzanne53]

OK, Jonmo1, that works, can you tell me why, though. so that I don't have to do this every time we import the data. Can you see any format problem with the original data? I don't have the import program, so don't know anything on that. Thanks a lot.

[Jonmo1]

It's called "Numbers Stored As Text"
It's pretty common with numeric data that is imported from external programs.
Even though the value appears to be numeric, Excel has it stored as a TEXT string.

Dates are really just numbers with special formatting.

Copying a blank cell is the same as copying a zero value.
Then the paste special - Values - Add then Adds the Zero to all the values in the range.

When you perform a math operation like + - * and / on a "Number Stored As Text", that converts it to a real number.

[Suzanne53]

Thanks a lot. I guess that is what I though it was but was trying to convert it to a number and thought that would work. I really appreciate it.