Input Mask

[Guest]

This is probably a dumb question but I am having some trouble when importing
data into excel. Excel seems to be taking my data and formatting it
automatically. For example I have a field that asks how many users were
affected. Excel will take the answer of 1-10 and interpret it as Jan-10 or
data that is 12-50 and interpret it as Dec-50. Does anybody know how to stop
excel from doing this? Thanks in advance and sorry about the lame question.

 

[georgesmailuk]

The cells need to either be formatted as text before the import, or
the data entered with ' before it to represent text
eg '12-50 will display as 12.50 if typed in.

 

[Guest]

The cells need to either be formatted as text before the import, or
the data entered with ' before it to represent text
eg '12-50 will display as 12.50 if typed in.




Thanks for the response George. But the file that I have is saved as a CSV file so excel opens it automatically. This cause the cells to interpret 1-50 as Jan-50. If I then try to format the cell as text I get something like 18264. Any ideas on how to stop excel on interpreting those items as dates?

 

[georgesmailuk]

I'm afraid not.

I just asked a similar question - am trying to get a date from csv
entered as text so that the date does not change from UK to US
format. I hope there's something i missed - you could take a look at
ADO to see if there is a database command to fix your problem. You
may have more luck than I.

George

 

[georgesmailuk]

My reply doesn't seem to have come up here.

I just posted a similar question regarding csv files as it is mixing
UK and US date formats and i can't get around it. I tried using ADO
code, but no joy. Going to look into the possibility of using a text
driver, but i'm at a loss where to start. The csv files i have are
larger than 65536 entries so my options are pretty limited if i want
to use excel. (splitting into multiple workbooks)

Will keep you posted if i find anything.

George

 

[georgesmailuk]

lol - can see my replies now.

After a fair bit of searching (still not sure if i can use it for
ADO), there may be a way.
Make a copy of your csv file. (Just incase)
rename it from csv to txt
when you open it in excel, the Import text wizard should open.
click on the relevent columns and change them from general to text.

They should now stay as desired.

Hope this helps.
George