Number format using TEXT( ) function

[T. Valko]

Hi Folks!

Using the TEXT() function, what format do I need to get:

$10
$10.50
$110.99
$1,110.99

Integers should remain integers but it should also handle decimals.

I tried $#,### which works fine on intergers but not on the decimals. Then I
tried $#,###.## which works fine on the decimals but not the integers.

Thanks

Biff

 

[Guest]

Maybe

=DOLLAR(A4,(MOD(A4,1)<>0)*2)

 

[T. Valko]

Thanks, J!

The TEXT() function is part of a VLOOKUP formula. I could work that DOLLAR()
in but I'd prefer to use TEXT() and just come up with the correct format
style if there is one!

Biff

 

[Guest]

Removing the unnecessary part <g>. Don't know how to do it w/Text.

=DOLLAR(A4,(MOD(A4,1)>0)*2)

 

[Ron Rosenfeld]

Hi Folks!

Using the TEXT() function, what format do I need to get:

$10
$10.50
$110.99
$1,110.99

Integers should remain integers but it should also handle decimals.

I tried $#,### which works fine on intergers but not on the decimals. Then I
tried $#,###.## which works fine on the decimals but not the integers.

Thanks

Biff

=TEXT(A8,IF(A8=INT(A8),"$#,###","$#,###.00"))

However,

=DOLLAR(A8,2*(A8<>INT(A8)))

gives the same result -- a dollar formatted text string with the desired
decimal, non-decimal formatting.
--ron

 

[Guest]

I know what you mean - I've tried before to figure this out w/o luck. Just
doesn't seem right that there is not a way to make the decimal optional.

The only other thing I can think of would be a UDF. Perhaps someone else
knows how to turn this trick.

 

[T. Valko]

Thanks guys!

Looks like DOLLAR() is the best choice.

Biff