Annoying & unrecognised value

[Digory]

Hoping someone may recognise this.

I regularly import data in to excel for use with various look up / count / if functions. Quite often I find that where I have pasted in values that they are not recognised until I go to the specific cell and hard return the value. I have tried cell format; general, number, text ect but makes no difference. I normally use paste special, values only so as not import any format or formula. Can be a real pain on large data sets........... mouse point, enter, next cell, mouse point, enter, next cell, ......

Ay ideas ?

D:-)

[Digory]

Maybe its just me - does this not happen to anyone else ?

D:-)

[Ron Rosenfeld]

On Mon, 10 Oct 2005 13:31:01 -0500, Digory
<[email protected]> wrote:

>
>Hoping someone may recognise this.
>
>I regularly import data in to excel for use with various look up /
>count / if functions. Quite often I find that where I have pasted in
>values that they are not recognised until I go to the specific cell and
>hard return the value. I have tried cell format; general, number, text
>ect but makes no difference. I normally use paste special, values only
>so as not import any format or formula. Can be a real pain on large
>data sets........... mouse point, enter, next cell, mouse point, enter,
>next cell, ......
>
>Ay ideas ?

Yes.

1. Formatting will not change the type of value. For example, if a number is
imported as TEXT, and you format the cell to be NUMBER, the value in that cell
remains TEXT. One way of changing every numeric appearing value to a number is
to select a blank cell; Edit/Copy; select your range where you imported;
Edit/Paste Special ADD

2. Sometimes when data is imported from a web sheet, there may be a trailing,
non-printable space -- char(160). This may need to be stripped off with a
formula before you can convert the underlying value to NUMBER.


--ron

[Digory]

Ron,

Thanks - will give it a go and let you know.

D:-)

[Ron Rosenfeld]

On Thu, 13 Oct 2005 14:33:59 -0500, Digory
<[email protected]> wrote:

>
>Ron,
>
>Thanks - will give it a go and let you know.
>
>D:-)

Thanks. By the way, the name for CHAR(160) should have been "no-break space".
It's sometimes used in HTML tables.
--ron