Number format using TEXT( ) function

T

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
 
T

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
 
J

JMB

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

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

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
Click to expand...

=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
 
J

JMB

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.
 
Top