ISNUMBER function problem

[Mark Frei]

I've imported portions of several different worksheets and
having problems with the ISNUMBER function. Certain cells
in the column that are numbers are recognized as text. If
I try to force them to numbers using the =VALUE(text)
function the actual text values in the column convert to
#VALUE! errors.
Any suggestions?
i.e. =IF(AND(ISNUMBER(A2),AND(A2>100000)),CONCATENATE(LEFT
(A2,2),",",MID(A2,3,3),"-",RIGHT(A2,1)),A2)

 

[Harlan Grove]

I've imported portions of several different worksheets and
having problems with the ISNUMBER function. Certain cells
in the column that are numbers are recognized as text. If
I try to force them to numbers using the =VALUE(text)
function the actual text values in the column convert to
#VALUE! errors.
Any suggestions?
i.e. =IF(AND(ISNUMBER(A2),AND(A2>100000)),CONCATENATE(LEFT
(A2,2),",",MID(A2,3,3),"-",RIGHT(A2,1)),A2)

There's nothing wrong with either Excel's ISNUMBER or VALUE functions. The
problems rest solely in your data, which almost certainly contain nonbreaking
space characters. See if

=VALUE(SUBSTITUTE(A2,CHAR(160),""))

produces the apparent numeric value of A2.