 # Multiple Format For Single Cell

• I was curious if it is possible to give a single cell multiple number formats based on what the number is in the cell.

So for example if my number is bigger than 1000, I would like to use comas so that it looks like this 1,000. However, if it is less than 1000 I would like it to look more like this 999.00.

Is this possible?

Thank you.

P.S.: Would prefer to stay away from VBA (at least in this case)

• Re: Multiple Format For Single Cell

i must be missing something here,
couldnt you just use the custom format: #,##0.00 ?

Cheers
___________
Xlite :sheep:
[st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

• Re: Multiple Format For Single Cell

Format a cell (right-click on the cell, select format) as a Number, determining how many decimal places you want it to display.

Then, insert this formula, which should work for any number below 1 million to 0.0 (however many decimal places chosen in format).

It looks at cell A1, which would contain the number you wanted formatted, so you'd need to change those references below for whatever cell needed:

=IF(AND(A1>=1000,A1<1000000,NOT(ISERROR(FIND(".",A1,1)))=TRUE),(LEFT(LEFT(A1,FIND(".",A1,1)-1),LEN(LEFT(A1,FIND(".",A1,1)-1))-3)&","&RIGHT(LEFT(A1,FIND(".",A1,1)-1),3)),IF(AND(A1>=1000,A1<1000000,NOT(ISERROR(FIND(".",A1,1)))=FALSE),(LEFT(A1,LEN(A1)-3)&","&RIGHT(A1,3)),A1))

If the numbers are above 1 million, you'll have to figure out how to take it from here.

• Re: Multiple Format For Single Cell

I think that would show the decimal places regardless, which I think he wanted to avoid in numbers above 1,000.

You could try something like #,###.## , but then you'd always show a decimal point regardless.

Quote from xlite

i must be missing something here,
couldnt you just use the custom format: #,##0.00 ?

• Re: Multiple Format For Single Cell

Why didn't I think of that? Nice.

Quote from ByTheCringe2

Try this:

[>=1000]#,##0;##0.00

• Re: Multiple Format For Single Cell

This is just great. Thank you so much.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!