Annoying & unrecognised value

D

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:)
 
R

Ron Rosenfeld

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 ?
Click to expand...

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
 
R

Ron Rosenfeld

Ron,

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

D:)
Click to expand...

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