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

"Dee Sperling" wrote:

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

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

"Dee Sperling" wrote:

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

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

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

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Dee Sperling" wrote:

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

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
"Dee Sperling" wrote in message
...
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

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

"Dee Sperling" wrote in message
...
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

Best Regards,

Gabor Sebo

"Dee Sperling" wrote in message
...

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.


"Dee Sperling" wrote:

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