confused by text cells.

[guthrie]

I have a sheet of data explored from an ACT database. ACT currently has a (known) new bug in that it adds a blank newLine to all fields.
So I made a copy of the sheet with every cell having a "=clean(..)" of the corresponding original cell.
Then I copied all of that , and did a paste as values into a new blank sheet.

The result looks ok, but the number cells are all text.
I changed the format of them from text to numeric - no effect.
then I made a copied column with "=value(..)" of each cell, but that gives a "#value!" error (B4, B5).
I tried the "*1" trick, that gives a "#value!" error (C6,C7).

Oddly, only the first two cells (A2,A3) give the error "number stored as text" with a conversion option.

I haven't yet tried all the other conversion tools/tricks, but why are these simple conversions not working?

[6StringJazzer]

The numbers are preceded by the character with the code 63, but it's not visible. This is normally rendered as a question mark but does not display as a question mark. I suspect that it is a UNICODE character that cannot be decoded by the CODE function. I don't know why CLEAN does not remove it.

Here is what the 1.68 in row 4 looks like. I think you just need a more comprehensive way to clean your data.

Values as displayed

	L	M	N	O	P
4	63	49	46	54	56
5		1	.	6	8

Underlying formulas

	L	M	N	O	P
4	=CODE(MID($A4,COLUMN(A4),1))	=CODE(MID($A4,COLUMN(B4),1))	=CODE(MID($A4,COLUMN(C4),1))	=CODE(MID($A4,COLUMN(D4),1))	=CODE(MID($A4,COLUMN(E4),1))
5	=MID($A4,COLUMN(A4),1)	=MID($A4,COLUMN(B4),1)	=MID($A4,COLUMN(C4),1)	=MID($A4,COLUMN(D4),1)	=MID($A4,COLUMN(E4),1)

[johnnyL]

I would suggest a formula to convert the values in Cell $A4 for example, etc. to the numeric values:

Please Login or Register  to view this content.