Cell format requires manual edit

L

landen99

I have a problem with changing the format of a pure number in a cell
formatted as a text. After the type is changed to a number (actually
it's changed to special - 000000), the cell refuses to change until
after the cell is edited (with or without changes) and the ENTER key is
pressed. Is there an easy way around this?

A similar thing happens with a cell has a special enter character.
Editing the cell and pressing enter changes the cell format to word
wrap and to recognize the special character, though the character is
not usually desired. I know how to delete the special characters with
a find and replace, but it illustrates how excel refuses to change the
cell formats until after you edit each cell. That can take a lot of
time with thousands of entries, like the document database that I'm
working with. Any suggestions?

Thanks in advance,
Andy Landen
 
L

landen99

Amazing how I discover a quick solution immediately after posting the
message. Click on a problem cell and you see a yellow exclamation
mark. Click on it and choose convert to number. On a massive scale,
select a group of cells using the shift key, click on the yellow
exclamation mark, and then choose convert to number.
 
L

landen99

I still have a problem with some of the data. It fits the criteria
specified at the link:
http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm,
but it doesn't respond to the blank cell arithmetic idea.
Editing the cell, I find an extra "space" after the numbers, but find
and replace doesn't see it. I just resolved the issue by using the
LEFT function to grab the appropriate number of digits and then convert
the resultant values to numbers. Thanks for the link and the help.

Andy Landen
 
A

Anne Troy

Hi, Andy. =TRIM(a1) will also remove any non-printing characters from a
cell. You might want to use that next time in case there's more than one
space. Glad you got it sorted!
************
Anne Troy
www.OfficeArticles.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top