rdounding numbers with text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Because not every number has a (c). That's why I believe an =if function will
do ther trick.
something like:
=if(a2,5.234(?), then round( 5.23(?), else round(a2,2))

Please advise

Thank you
 
Assuming all entries follow this format:

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

=ROUND(LEFT(A1,FIND("(",A1&"(")-1),2)+0
 
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
 
Do you think that following will work?

=IF(ISNUMBER(SEARCH("(?)",H2)),(ROUNDUP(LEFT(H2,4),2))&RIGHT(H2,3),LEFT(H2,4))
 
Thanks a million

T. Valko said:
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
Click to expand...
 
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.
 
The formula has nothing to do with the formatting, you set the format for
the cell
using format>cells>number 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
 
Back
Top