Why do some number appear as text?

[Rock]

Hi,

I have just exported some data in a table from an sql file to Excel and
have noticed that some of the figures in the column have a small green
triangle next to it with the message, 'it is formatted as text or has an
apostrophe in front of it'.

What does this mean?

I am trying to change a column of 0's to 1's then import it back into
sql, but after replacing the 0's with 1's, find that some have this
triangle.

I have searched the help files but can't find anything to help me.

Thanks

Rock

 

[Anne Troy]

Excel is seeing them as text; it's very common. See "Data isn't
recognized..."
http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm

************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

 

[David McRitchie]

Hi Rock,
Preceding a value with a single quote is one way of formatting
a cell as text, the other way is through format, cells, text (or customize)

I don't know what you are going to want for SQL (a number, or text)
but since you as why some appear as text, I would take that to e
mean you want numbers.

So format the column as General, then run the TRIMALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

If you want a non macro approach, format as number but then
select an empty cell and copy it (ctrl+C) then select the
column to be converted, edit, paste special, ADD

Now you want to convert the 0 to 1, and 1 to 0 so you would
use a helper column (another column)
=IF(TRIM(C1)="","",IF(C1=1,0,1))
You only want to put the formula down as far as you have data
in column C.