LinkBack URL
About LinkBacks
I have two numbers in a single cell seperated by a dash. Is there a way to format them so they display as currency? It may be that sometimes the cell only displays one number.
Thanks in advance.
I have two numbers in a single cell seperated by a dash. Is there a way to format them so they display as currency? It may be that sometimes the cell only displays one number.
Thanks in advance.
Hello Terghan ,
Can you provide a few examples of numbers in this format?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hi there
I don't think there's any way using normal Excel (although I am forever suprised by the things people come up with around here!)
I think you will need a User Defined Function (UDF):
This one has the following variables:
Please Login or Register to view this content.
strFormat - format of the numbers. e.g. "$0,0.00"
Val1 - first value. e.g. 2000
Optional strSeparator. If this is present, it will decide it must format a range. e.g. " - "
Optional Val2. e.g. 3000.
You need to put this in to a module in your spreadsheet.
Alternatively, create it using built in Excel functions:
Best regards, Rob.Please Login or Register to view this content.
Last edited by rscsmith; 04-18-2012 at 06:37 PM.
Just so no one thinks I'm putting them off, I will further address this issue tomorrow with examples. Rob, I will also give your solutions a try, although they seem potentially more complex than necessary (I hope).
Hopefully my examples will make things a little clearer tomorrow.
Thanks!
You can likely do it with a pair of TEXT() functions - depends on your samples.
Alright, sorry for the delay. Here is what I'm looking at. This cell has a formula that calls for a number or numbers from a different cell, it displays the numbers like this:
1.17 - 1.20
I would like for them to be formatted to look like this:
$1.17 - $1.20
Added bonus, here's one of the formulas that calls for information from a seperate cell.
Please Login or Register to view this content.
Last edited by Terghan; 04-19-2012 at 04:04 PM.
Also, same issue but with percentages in a different cell.
No guarantees - try this for the currency result
Please Login or Register to view this content.
Success! And I see what you did there, so I'm going to begin altering the rest of my formulas, to fix this issue. What would I replace $0.00 with for percentages? 0%?
Thanks!
Try "0.00%" but you may have to divide the first argument by 100 if the results are out of whack.
Like so: TEXT((blahblahblah)/100,"0.00%")
Fantastic. Thanks for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks