Number with dash is importing as date

  • Thread starter Thread starter Dee Sperling
  • Start date Start date
D

Dee Sperling

An Excel spreadsheet is being exported from a SQL database that I cannot
change, so I need to accomplish this in Excel.

Sometimes, some of the many numbers contain a dash which Excel then
interprets as a date. I can remove the dash programatically, but the number
is not correct afterwards.
For example, the number imported is 05-9078 which is displayed in Excel as
May-78, but is actually stored as 5/1/9078. Changing the cell format to
number or text gives me 2621833.

I need to be able to add to my macro something that will give me back the
original number so that I can remove the dash and end up with 059078

Any help greatly appreciated.

Dee
 
Not sure of all the particulars, but, with your specific example, the
following statement did what you're asking for with a single step:

ActiveCell.NumberFormat = "mmyyyy"

When I entered 05-9078 in the cell, it behaved exactly as you said, and
exactly as I would have expected and converted it to May-78, with 5/1/9078
displayed in the formula bar. I entered the above statement in the immediate
window and the cell then displayed 059078 - just as requested.

HTH
Bill
 
As I re-read your post, I noticed that you said 'many' of the numbers contain
a dash. Since Excel automatically converts these to the date format of
"MMM-yy" you should probably check the cells for that number format before
executing the conversion.

For Each Cell in Range(..enter your range here..)
If Cell.NumberFormat = "MMM-yy" Then
Cell.NumberFormat = "mmyyyy"
End If
Next

I think this should do what you're looking for.
HTH
Bill
 
Put this in a loop going down the column of data. If Excel thinks it's a
date, it'll change the value to text.

If IsDate(ActiveCell.Value) Then
ActiveCell.Value = "'" & Format(ActiveCell.Value, "mmyyyy")
End If
 
Hi Dee,


I think you would like to import the data including the dash.
I suggest you try to import this column as text.

HTH,

Wouter
 
Hello Dee,

If you could change 05-9078 into 05_9078 then the text would not turn into
a date.


I hope that this is doable.

Regards,

Gabor Sebo
 
Hello Dee,

I framed in a group cells.Then: Home,Format Cells and Text. Now it was
possible to enter 05-9078 without it being turned into a date 7000 years
from now.

Best Regards,

Gabor Sebo
 
Thank you all for your suggestions, but every one of them still results in an
incorrect number when the data is copied to the new spreadsheet. For
example, 02-2011 turns into 40575.
 
I had the same problem importing dashed numbers from a Word 2007 document into an Excel 2007 spreadsheet, no matter how I set the format of the receiving cells.

I finally solved the problem by copying a column of the numbers from Word and pasting them into a .txt environment (like Notepad) to strip out any formats from the numbers. Then I copied them from there and pasted them into a text formatted column in Excel; the numbers displayed exactly as they had in the Word doc.

Because this worked, I suspect the original dashed numbers carried with them some sort of Word format that didn't translate well in Excel. Probably using an app like PureText, which also strips formatting, would work as well.
 
Last edited:
Back
Top