software

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?--
geoffwright
------------------------------------------------------------------------
geoffwright's Profile: www.excelforum.com/member.php...oamp;userid=31801
View this thread: www.excelforum.com/showthread...hreadid=515257Are you sure you formatted the cell as quot;textquot;? 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
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--
geoffwright
------------------------------------------------------------------------
geoffwright's Profile: www.excelforum.com/member.php...oamp;userid=31801
View this thread: www.excelforum.com/showthread...hreadid=515257Hi 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
=quot;'quot;amp;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
NAVEENquot;geoffwrightquot; wrote:

gt;
gt; I want a column to have data in any format, especially like
gt; 3/1/32
gt; and leave it in that format i.e. NOT to try and convert it to a date
gt;
gt; other rows are left untouched as they are obviously not dates e.g.
gt; 28/7/4/13
gt;
gt; Both text and general format convert 3/1/32 to a long 11691
gt;
gt; Is it possible to force this not to convert?
gt;
gt;
gt; --
gt; geoffwright
gt; ------------------------------------------------------------------------
gt; geoffwright's Profile: www.excelforum.com/member.php...oamp;userid=31801
gt; View this thread: www.excelforum.com/showthread...hreadid=515257
gt;
gt;


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--
geoffwright
------------------------------------------------------------------------
geoffwright's Profile: www.excelforum.com/member.php...oamp;userid=31801
View this thread: www.excelforum.com/showthread...hreadid=515257When you import a csv file, don't double click or open it, select
data--gt;get external data--gt;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
quot;textquot; for the date column. That should work.

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

Many Thanks--
geoffwright
------------------------------------------------------------------------
geoffwright's Profile: www.excelforum.com/member.php...oamp;userid=31801
View this thread: www.excelforum.com/showthread...hreadid=515257