Conditional custom number format?

[Dawn]

Is there a way to make a conditional custom number format? I have two
formats that ALMOST work for what I need, but not quite. #.# works for most
numbers, but if there is no decimal in the number it displays an unnecessary
decimal. ## works for most numbers EXCEPT for ones with a decimal, which it
cuts off on the end. Can I make excel apply #.# to any cell with a decimal
in it and ## to all the rest? Or is there some other custom number format
that will work? Here is a sampling of the numbers I need to display
properly, but all need to have the same format applied to them:

5856792.6
2007-527689
10-2007-7000490
200580022150.1
11/501,207
4704825.1
60/441,759
ABC/US04/01700


I am trying to get around the problem of having numbers alrger tha 12 digits
display in scientific notation. Usually, it would work to change to text
format but ONLY if that's done prior to the number being entered. I am
having to work with a huge report produced from a database which
automatically generates with the "general" format on all the cells. My only
option is to apply a format after the fact, and text format will not
automatically update the display of those 12+ digit numbers. But number
formatting will, if I can just find the right format.

Thanks for any ideas!

 

[GB]

Have you tried formatting as text? I have just tried doing that after the
number is entered and it works for me with Excel 2003.

 

[GB]

Have you tried formatting as text? I have just tried doing that after the
number is entered and it works for me with Excel 2003.

oops, no, it messes up with 200580022150.1

 

[Dawn]

Thanks, indeed I have tried that -- it only works if I can find the cell, the
double click in it. THEN excel will display the field properly, and flags it
as a number formatted as text. Not very useful for a huge report like I
have, alas. I've also tried copying and pasting the column into a new column
formatted as text, I've tried clearing the formatting and reapplying, the
closest I've come is the number format solution but I just can't get it
exactly right. I am boggled that excel is making this so hard; and in
researching I can see LOTS of people run into this problem, so I'm not sure
why there is not a reasonably easy way to fix it.

 

[GB]

Try setting up a second column with =left(A1,30) (that's assuming your data
is in column A)
That seems to force a conversion to text, and then it takes the leftmost 30
digits, which is I guess enough for you - else increase it.

 

[Dawn]

Brillant, that works perfectly, thank you so much! I'm saved from having to
send a crazy email to my client trying to explain this weird problem, yay!

[for anyone else following the thread, if you have to get rid of the old
column that displays improperly like I do (I can't just hide it because the
number and specific order of columns is a strict formatting requirment of the
client who gets this report) I will copy the new column and "paste special"
and choose "values" which will turn it all to text, getting rid of the
formula. Then I can delete the original column that displays improperly.
This can all be done with a recorded macro (I'm no programmer!)]