rdounding numbers with text
 

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

rdounding numbers with text
 

Why not make the (c) part of the custom format: 0.00 (c)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"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

rdounding numbers with text
 

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



"Bernard Liengme" wrote:

Why not make the (c) part of the custom format: 0.00 (c)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"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

rdounding numbers with text
 

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

rdounding numbers with text
 

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

rdounding numbers with text
 

Do you think that following will work?

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

"Bernard Liengme" wrote:

Why not make the (c) part of the custom format: 0.00 (c)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"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

rdounding numbers with text
 

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

rdounding numbers with text
 

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

rdounding numbers with text
 

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

rdounding numbers with text
 

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