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:
Function FormatRange(strFormat As String, Val1 As Single, Optional strSeparator As String, Optional Val2 As Single) As String Dim strOut As String strOut = Format(Val1, strFormat) If strSeparator <> "" Then strOut = strOut & strSeparator & Format(Val2, strFormat) End If FormatRange = strOut End Function
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.=TEXT(A1,"$0,0.00 - ")&TEXT(A2,"$0,0.00")
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.
Added bonus, here's one of the formulas that calls for information from a seperate cell.
=IF(OR(COUNTBLANK(B37),COUNTBLANK(B42)),B46,IF(ISNUMBER(B42+0),IF(ISNUMBER(B37+0),ROUND(B42/B37,2),ROUND(B42/LEFT(B37,FIND("-",B37)-1),2)&" - "&ROUND(B42/MID(B37,FIND("-",B37)+1,100),2)),IF(ISNUMBER(B37+0),ROUND(LEFT(B42,FIND("-",B42)-1)/B37,2)&" - "&ROUND(MID(B42,FIND("-",B42)+1,100)/B37,2),ROUND(LEFT(B42,FIND("-",B42)-1)/LEFT(B37,FIND("-",B37)-1),2)&" - "&ROUND(MID(B42,FIND("-",B42)+1,100)/MID(B37,FIND("-",B37)+1,100),2))))
Last edited by Terghan; 04-19-2012 at 04:04 PM.
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
Also, same issue but with percentages in a different cell.
No guarantees - try this for the currency result
=IF(OR(COUNTBLANK(B37),COUNTBLANK(B42)),B46,IF(ISNUMBER(B42+0),IF(ISNUMBER(B37+0),ROUND(B42/B37,2),TEXT(ROUND(B42/LEFT(B37,FIND("-",B37)-1),2),"$0.00")&" - "&TEXT(ROUND(B42/MID(B37,FIND("-",B37)+1,100),2),"$0.00")),IF(ISNUMBER(B37+0),TEXT(ROUND(LEFT(B42,FIND("-",B42)-1)/B37,2),"$0.00")&" - "&TEXT(ROUND(MID(B42,FIND("-",B42)+1,100)/B37,2),"$0.00"),TEXT(ROUND(LEFT(B42,FIND("-",B42)-1)/LEFT(B37,FIND("-",B37)-1),2),"$0.00")&" - "&TEXT(ROUND(MID(B42,FIND("-",B42)+1,100)/MID(B37,FIND("-",B37)+1,100),2),"$0.00"))))
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