PDA

View Full Version : How do I stop Excel from converting this to a date?


Course 4 Escapee
01-16-2004, 11:50 AM
I copied a text string "8-5" from a web page and pasted it into Excel. Excel then converted that string into a date value of 8/5/2004. Is there some way to stop this overzealousness?

:swear:

Sunny
01-16-2004, 11:52 AM
If you are just concerned about this one cell, easy, just type ' before 8-5 then voila, there you have it.

If you have a whole columns of millions of cells that you want to convert, I like to export to access and that'd also be cake.

Hope that helps.

Bama Gambler
01-16-2004, 11:55 AM
Paste Special as Text

NoName
01-16-2004, 12:12 PM
If you just paste special as text it will convert, at least in Excel 2000. However, if you format the cell as text and then paste special as text, it will leave it alone.

Cho Da
01-16-2004, 12:13 PM
Format the cell to TEXT before pasting.

Course 4 Escapee
01-20-2004, 10:35 AM
Let me clarify: I am copying 20 columns of data, with several columns having data of the form "8-5" or "12-1" which gets converted to 8/5/2004 or 12/1/2004. If I take the suggestion to format the cells as text before hand Excel just overrides that formatting and does a date conversion on the text string. If I convert the columns after the paste to text, then it just converts it to a numerical value (36600+). If I Paste Special, then it squeezes the entire data set into one column, space delimited.

Sunny
01-20-2004, 11:12 AM
Converting the cells to text doesn't work. Try Access, I just did something like that to help my boss last week and it worked beautifully.

NoName
01-20-2004, 11:24 AM
If you have several columns, then you can do "text To Columns" after pasting as text, making sure to set the type of each column to Text in the text-to-columns wizard.

Brad Gile
01-20-2004, 11:30 AM
Let me clarify: I am copying 20 columns of data, with several columns having data of the form "8-5" or "12-1" which gets converted to 8/5/2004 or 12/1/2004. If I take the suggestion to format the cells as text before hand Excel just overrides that formatting and does a date conversion on the text string. If I convert the columns after the paste to text, then it just converts it to a numerical value (36600+). If I Paste Special, then it squeezes the entire data set into one column, space delimited.

OK. Convert "datelike" strings to a string that Excel will not automatically convert to a date by putting a single quote in front of it, like this:

Sub FixString()
Dim X As String

X = InputBox("x")
If InStr(X, "-") Then
'X is "datelike"
'so put a single quote in front of it
'so that Excel will accept it as String
'and NOT convert to date!
X = "'" & X
End If
Range("b5").Value = X
End Sub

Thus, "7-11" gets converted to "'7-11", which Excel will NOT convert to a date. :D



Brad

Course 4 Escapee
01-20-2004, 11:48 AM
I'm copying data from a webpage to paste into Excel. I can't do anything to the source data to prep it for copying nor does it seem I can do anything to Excel to prep it for pasting.

There has to be SOME WAY to shut off this automatic data conversion.

Brad Gile
01-20-2004, 11:49 AM
In the same vein, you can create your own conversion function, like this:
Public Function ToString(ByVal S As Variant) As String
'Expresses S as a string that Excel will not convert
'automatically to something else!
Dim Z As String

Z = CStr(S)
If IsNumeric(S) Then
ToString = "'" & Z
Exit Function
End If

If InStr(Z, "-") Or InStr(Z, "/") Then
Z = "'" & Z
End If

ToString = Z

End Function

You will, however, have to remember that these "datelike" strings will now have a single quote in front. :D


Brad

Cho Da
01-20-2004, 12:03 PM
If I take the suggestion to format the cells as text before hand Excel just overrides that formatting and does a date conversion on the text string.

What version of Excel are you using? this WFM in Excel 2002 (Office XP).

Course 4 Escapee
01-20-2004, 12:05 PM
2000

Brad Gile
01-20-2004, 12:16 PM
If I take the suggestion to format the cells as text before hand Excel just overrides that formatting and does a date conversion on the text string.

What version of Excel are you using? this WFM in Excel 2002 (Office XP).

Yup. No problem in Excel XP. Also works in Excel 2003. Apparently, Excel 2000 works differently?


Brad

Cho Da
01-20-2004, 12:26 PM
If I take the suggestion to format the cells as text before hand Excel just overrides that formatting and does a date conversion on the text string.

What version of Excel are you using? this WFM in Excel 2002 (Office XP).

Yup. No problem in Excel XP. Also works in Excel 2003. Apparently, Excel 2000 works differently?


BradI found a pc still with 2000 on it. WFM there as well.

Course 4 Escapee
01-20-2004, 01:51 PM
:-?

I have no idea what else to try. VBA isn't any more effective than me just utilizing some Day(), Month() or Year() formulas to parse out the stuff and reassemble it as a text string.

Cho Da
01-20-2004, 01:54 PM
:-?

I have no idea what else to try. VBA isn't any more effective than me just utilizing some Day(), Month() or Year() formulas to parse out the stuff and reassemble it as a text string.What website are you copying? I am surprised that formatting the destination cells as TEXT (not General) isn't working.

Sunny
01-20-2004, 01:55 PM
C4E, have you tried importing your excel file into ACCESS and then make them text yet?

it always works for me.

Chuck
01-20-2004, 03:22 PM
You could also copy and paste into a text file and then use the import function in Excel to import the text file (where you can define whether a field is a date or text).

Chuck

Course 4 Escapee
01-20-2004, 04:34 PM
You could also copy and paste into a text file and then use the import function in Excel to import the text file (where you can define whether a field is a date or text).

Chuck

I tried this, but unfortunately due to the irregular nature of the data in question, importing using space delimiting or fixed columns doesn't work. It's just exchanging one problem for another.

Course 4 Escapee
01-20-2004, 04:36 PM
:-?

I have no idea what else to try. VBA isn't any more effective than me just utilizing some Day(), Month() or Year() formulas to parse out the stuff and reassemble it as a text string.What website are you copying? I am surprised that formatting the destination cells as TEXT (not General) isn't working.

http://www.collegerpi.com/subs/cy/top105.html

[It's only accessible to subscribers]

I'm trying to analyze home court advantage in college basketball. Yes, this is for work :D

Cho Da
01-20-2004, 05:35 PM
http://www.collegerpi.com/cy/rpi.html

I did the table in this page.

Copy, paste to notepad.

In excel: Format column C to TEXT.

Copy from notepad

paste to cell a1.

WFM.

Modeler
01-21-2004, 03:41 AM
I'm trying to analyze home court advantage in college basketball. Yes, this is for work :D

Yes, we are high-tech bookies! :P