| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
| VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
| Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
| How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
| How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Linear Mode
