How to format two numbers into two currency units, with a "to" between them?

[this_is_pissing_me_off]

Hello.
I am creating a simple inventory, and for one of the columns I would like to have a range of sorts. In theory it would look like "$30 to $50". I was wondering if there were a way I could just enter "30 50" and it would format it to two separate currency units with a "to" between them?

Thank you.

 

[farmerscott]

Hello,

welcome to MrExcel.

If you already have $30 and $50 in separate cells (for your sort) then try

=A1& "to" &B1

FarmerScott

 

[dermie_72]

if you had 30 in on cell, and 50 in another (say using a min column, and a max column, you could then use & to join the cells.
IE if min was A1, max was B1, the join was C1
A1 = 30
B1 = 50
C1 = A1&" to "&B1

if you put in C1 "=if(A1="","",A1&" to "&B1) then it will only populate when you enter data in the min.
if you wanted it to be dependent on both

C1 =if(and(A1<>"",B1<>""),A1&" to "&B1,"")

 

[this_is_pissing_me_off]

I see what you're saying,and I'm sure I'll probably have to go that route, but is it at all possible to just enter "50[space]30" in a cell and have a custom formatting convert them into two separate currency units with a "to" between them. I have tried "$#,##0 " to" $#,##0" , but when I enter two numbers like this "50 30" it does nothing but oddly (well odd for my level of understanding) when I enter one number like "50" it formats to "$50 to $0". Is there a syntax that could allow me to input for that second currency unit?

 

[Rick Rothstein]

I see what you're saying,and I'm sure I'll probably have to go that route, but is it at all possible to just enter "50[space]30" in a cell and have a custom formatting convert them into two separate currency units with a "to" between them. I have tried "$#,##0 " to" $#,##0" , but when I enter two numbers like this "50 30" it does nothing but oddly (well odd for my level of understanding) when I enter one number like "50" it formats to "$50 to $0". Is there a syntax that could allow me to input for that second currency unit?

No, you will need to use VBA event code to do that. Assuming the column you want to have this functionality is Column B (change the column letter designation in the red highlighted text below if B is not right)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim CellsInColumn As Range, Cell As Range, Parts() As String
  
  Const ColumnToMonitor As String = "B"
  
  Set CellsInColumn = Intersect(Target, Columns(ColumnToMonitor))
  If Not CellsInColumn Is Nothing Then
    For Each Cell In CellsInColumn
      If Not Cell Like "*[!0-9 ]*" And Cell Like "* *" And Not Cell Like "* * *" Then
        Parts = Split(Cell.Value)
        Cell.Value = Format(Parts(0), "$0") & " to " & Format(Parts(1), "$0")
      End If
    Next
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[dermie_72]

i don't believe it is possible with the cell formatting feature, however it would be possible with an automacro, but if you're not proficient in VBA, then the option of having 2 cells may be your most efficient option.

 

[this_is_pissing_me_off]

WOW! That works perfectly! Thank you very much, and also thanks to everyone that took the time out of their day to reply.
Wonderful community.
Thanks, thanks, thanks.