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

You can force Excel to see text as numbers (provided, of course, they're
numbers in the cells). See "Data Isn't Recognized..."
http://www.officearticles.com/excel/...soft_excel.htm
************
Anne Troy
www.OfficeArticles.com

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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.

I still have a problem with some of the data. It fits the criteria
specified at the link:
http://www.officearticles.com/excel/...soft_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

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I still have a problem with some of the data. It fits the criteria
> specified at the link:
> http://www.officearticles.com/excel/...soft_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
>