Hell All -

I am importing data from the web. On the web the data is a phone
number: 4-3231

When it gets to Excel, Excel says "Aha! A date, no doubt!" and turns it
into 4/1/3231 and displays Apr-31, with a new custom mmm-yy format.

Is there a VBA way to turn off this Excel magic and leave April 1, 3231
as the string it used to be: 4-3231?

I tried to pre-format the cell as "General" in which case Excel turns it
into the serial date 486230. No help.

The best I've come up with so far is to test

If IsDate(GetPhone) Then GetPhone = "'" & Month(GetPhone) & "-" &
Year(GetPhone)

adding a single quote at the start to coerce a string. My sense is that
there is a more elegant way.

Can anyone chime in?


Thanks in advance.
....best, Capt N.

--
Email to (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy

If you're importing using a Web Query you can set it under Options to not
turn those into dates.

Web Query dialogOptionsDisable date recognition.


Gord Dibben MS Excel MVP


On Tue, 17 Jul 2007 19:51:18 -0400, wrote:

Hell All -

I am importing data from the web. On the web the data is a phone
number: 4-3231

When it gets to Excel, Excel says "Aha! A date, no doubt!" and turns it
into 4/1/3231 and displays Apr-31, with a new custom mmm-yy format.

Is there a VBA way to turn off this Excel magic and leave April 1, 3231
as the string it used to be: 4-3231?

I tried to pre-format the cell as "General" in which case Excel turns it
into the serial date 486230. No help.

The best I've come up with so far is to test

If IsDate(GetPhone) Then GetPhone = "'" & Month(GetPhone) & "-" &
Year(GetPhone)

adding a single quote at the start to coerce a string. My sense is that
there is a more elegant way.

Can anyone chime in?


Thanks in advance.
...best, Capt N.

In article ,
Gord Dibben <gorddibbATshawDOTca wrote:

If you're importing using a Web Query you can set it under Options to not
turn those into dates.

Web Query dialogOptionsDisable date recognition.


Gord Dibben MS Excel MVP


Hi Gord -

Thank you for the insight. I never noticed it in the recorded settings
before you mentioned it. I made the appropriate correction in the Web
query VBA. Turns out that was only the first half of the problem
however.

The worksheet that was storing all the retrieved phone numbers also saw
it as a date after I took out the ISDATE() test.

To fix that, I preset its .numberformat to "@"

Now the strings stay strings, and it feels a little cleaner.

Thanks. Is there a way to turn on and off this "clairvoyance" (probably
has an official MS name) in VBA or otherwise?

....best, Capt N.

--
Email to (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy