how to stop excel formatting

[geoffwright]

I want a column to have data in any format, especially like
3/1/32
and leave it in that format i.e. NOT to try and convert it to a date

other rows are left untouched as they are obviously not dates e.g.
28/7/4/13

Both text and general format convert 3/1/32 to a long 11691

Is it possible to force this not to convert?

 

[flummi]

Are you sure you formatted the cell as "text"? When I format a column
as text and enter a date it surely doesn't do any formatting.

But if you type an apostrophe preceding the date it should treat it as
text anyway.

Hans

 

[geoffwright]

I put the two lines below into a csv file without any quotes and opened
it with excel. Line 2 displays no problem, line 1 is converted into a
long 11691 with either TEXT or GENERAL format. I want the column left
in its original format but excel insists on trying to convert anything
that looks like a date

3/1/32
28/7/4/13

 

[Guest]

Hi geoffwright,

Yes. You are correct. When you enter something like date Excel will take it
as Date and again when you format it, it will show some strange number. That
number is the number of minutes ... .

The solution for it is very simple ...
- Assuming you data is in Column-A, Insert a blank column after Col-A i.e.,
Col-B
- Type the following formula in Col-B
="'"&A1

(don't confuse it is just Equal to and double quotation and single quotation
and double quotation and amprasend symbol and A1)

- Copy it to following cells
- Select all the cells in this column, whichever you have copied
- Copy
- Paste in Col-A

Done



Regards
NAVEEN

 

[geoffwright]

I had thought of something like that but this formats the two lines as
03/01/1932 '11691
28/7/4/13 '28/7/4/13

 

[flummi]

When you import a csv file, don't double click or open it, select
data-->get external data-->import text file
selecet the csv file and go through the subsequent dialogue step by
step. One screen lets you define the format of every column. Select
"text" for the date column. That should work.

Hans

 

[geoffwright]

Well done Flummi,
that forces it to be text rather than date/minutes

Many Thanks