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.

 

[wardw]

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.