Hi
I have a financial spreadsheet that I imported from a CSV file. All of the
number cells are showing the number left justified, which I think means that
Excel sees them as text. However when I click on "Format Cells:Number", the
Category setting is "General". If I attempt to apply any of the procedures in
the help pages, like multiplying the content by 1 (to convert text to number)
that operation seems to fail as it does not result in producing a number
either.

How can I convert these cells to proper numbers that will respond to
formulas and conditional formatting?

Thanks in advance.

If you click up in the formula bar on one of these cells, can you tell
if there are what appear to be trailing spaces after the numbers?

If there are, and they're really just spaces, multiplying by 1 should
do the trick; however, if they appear to be spaces, they may be some
other odd character that is not a space.

One way to test if they are merely spaces is to try to create a column
referencing this one with the formula =TRIM(A1). Then copy that column
of those formulas, and paste special -- values. If they were truly
spaces, if you click up in the formula bar on one of the cells, they'll
be gone now. if they were some other funky character, they'll still be
there. In such a case, you may have to resort to using the LEFT or MID
or RIGHT functions to strip out those characters.

Let me know if you need help.


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=399315

Thanks much, Excel_Geek. All were excellent suggestions, but alas, to no
avail. For future reference, and for others who may be suffering from the
same problem, here is what I finally did to fix it:

Conclusion:
Number cell contains appended nonprinting characters preventing Excel from
formatting or treating cell as a number. Could also be confirmed by typing
into the formula bar after the number and observing what appeared as a space
between the number and the typed characters.

- To further confirm, I saved the data as a csv file, then imported into
Access AS TEXT. Access marked all the nonprinting characters with an 'a
symbol. Even though Access was recognizing the nonprinting character, and
rendering it as a funky symbol, it still would not allow a find and replace.

-I went back into the .csv file with notepad, selected all the text, opened
the find/replace dialog and copied the comma+blank-space sequence into the
Find field and put just a comma into the Replace field.

Viola - the offending non-printing character is now gone and I can import
all the data into either Excel or Access and have the program recognize the
number fields as proper numbers.

"Excel_Geek" wrote:


If you click up in the formula bar on one of these cells, can you tell
if there are what appear to be trailing spaces after the numbers?

If there are, and they're really just spaces, multiplying by 1 should
do the trick; however, if they appear to be spaces, they may be some
other odd character that is not a space.

One way to test if they are merely spaces is to try to create a column
referencing this one with the formula =TRIM(A1). Then copy that column
of those formulas, and paste special -- values. If they were truly
spaces, if you click up in the formula bar on one of the cells, they'll
be gone now. if they were some other funky character, they'll still be
there. In such a case, you may have to resort to using the LEFT or MID
or RIGHT functions to strip out those characters.

Let me know if you need help.


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=399315