Number with dash is importing as date

[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

 

[fisch4bill]

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

 

[fisch4bill]

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

 

[Gary Brown]

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

 

[Wouter HM]

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

 

[helene and gabor]

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

 

[helene and gabor]

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

 

[helene and gabor]

Best Regards,

Gabor Sebo

news:[email protected]...

 

[Dee Sperling]

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.