The formula has nothing to do with the formatting, you set the format for
the cell
using formatcellsnumber etc. The only way to change the format is to use
an event macro
The only way to change "formats" using a formula (and that is fairly
limited) is to use the TEXT function. Of course the values will be text and
not numbers but that shouldn't matter too much if you are only interested
in the display


--


Regards,


Peo Sjoblom


"Iguss" wrote in message
...
Good Morning,

Would you possible know the reason why this formula
=ROUND(LEFT(A1,FIND("(",A1&"(")-1),2)&MID(A1,FIND("(",A1&"("),255) doesn't
returns following
3 as 3.00
3.1 as 3.10
3.3(c) as 3.30(c)

After copying and pasting as special values and them formating results as
numbers with two decimal places I still don't get 3.10 or 3.00.

I even tried to copy, paste special and multiply.

Does this have to do something with excel set up, should any options be
changed?

Thank you for you help.





"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...
Thanks a million

"T. Valko" wrote:

Ooops!

I guess you want to keep the (...) if it's there?

Try this if that's the case:

=ROUND(LEFT(A1,FIND("(",A1&"(")-1),2)&MID(A1,FIND("(",A1&"("),255)

Note that this will return TEXT even if the entry doesn't have a
(...):

5.020(c) returns the text value 5.02(c)
1.025 returns the TEXT value 1.03

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assuming all entries follow this format:

number(text) = 5.020(c)
number = 5.020

=ROUND(LEFT(A1,FIND("(",A1&"(")-1),2)+0

--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...
Would you please advise of =If formula where I can round 5.020(c)
so
It
displays something like that at the end 5.02(c) or any other letter
that
can
show up in the field.

Thank you