Leading Quote in Text Strings

Originally Posted by nsv

In my own Excel setup I can always recognize if a cell contains text beause it has a quote leading the content (or a circonflex or a double quote), also when this text consists only of numbers.

However, I had to help a collegue with sorting out a lot of confusion because he had imported different datasets containing dates in different formats. Some were 28.01.2006 while others were 28-01-2006 and sometimes Excel regarded them as dates, ie. stored them as numbers regardless of the divider being . or - and sometimes they were considered text.

To my surprise in his setup there are no quotes indicating text, so I wasted some time before I found out what was happening.
How can I change his setup to show the text indicating quotes?


NSV

Hi,

if this data has been imported you may, or may not, see the (psuedo) ' indicating a text (considered) cell.

try a =Len(A1) or a =CODE(LEFT(A1,1)) to see whether the item is so coded, or post a sample of a few such cells in a .zip file.

hth
---

Originally Posted by nsv

In my own Excel setup I can always recognize if a cell contains text beause it has a quote leading the content (or a circonflex or a double quote), also when this text consists only of numbers.

However, I had to help a collegue with sorting out a lot of confusion because he had imported different datasets containing dates in different formats. Some were 28.01.2006 while others were 28-01-2006 and sometimes Excel regarded them as dates, ie. stored them as numbers regardless of the divider being . or - and sometimes they were considered text.

To my surprise in his setup there are no quotes indicating text, so I wasted some time before I found out what was happening.
How can I change his setup to show the text indicating quotes?


NSV

select a new blank sheet (Ctrl+A)
right click > Format cells > and format it as Date
copy imported data from your sheet and paste special as values in new sheet (which is formatted as Date)
hope this would work.

Originally Posted by nsv

Thanks, but the problem is not to sort out what is text and what is numbers - there are several ways of doing that, so what I'm looking for is the difference between the setup of my collegue's Excel and mine.
Why do my computer always show the text indicator (not in the cell of course, only in the upper line - I don't know what it is called - where you can edit the cell contents after pressing F2) while other people's computers don't?

that specific option is covered in Tools, Options, Edit, Edit directly in Cell.

Ticked allows this,
unticked doesn't.

hth
---

Originally Posted by nsv

Just found out... several years ago I used to be a Lotus 123 shark. Switching to Excel was not easy - I had the Lotus123 nav. bottons in my fingers so of course I ticked that in the Tools - Options, and this is what causes the formula line to show the text indicator.

Anyway - thanks for your interest...

NSV

it's just good to see that you have resolved your problem, and thanks for the feedback.
---