combining number formats
combining number formats
(OP)
I apologize if this is too basic.
How do I combine multiple number formats for single cell
0;-0;;@ (ignore zero's)
#,### (add comma's)
How do I combine multiple number formats for single cell
0;-0;;@ (ignore zero's)
#,### (add comma's)
=====================================
Eng-tips forums: The best place on the web for engineering discussions.





RE: combining number formats
More than one number for a single cell?
You can apply different formats for characters in a single cell, but if you put a number it will be recognized as only a number.
If you want to put more than one number with different formats, you may only do it if you declare the cell as a text. You may do it adding the apostroph ' before the number. If you want ot have it centered, add the character ^ before the number.
It will be taken as a text, and you may format the cell character by character.
Best regards
J.Alvarez
RE: combining number formats
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'
' two number formats for a single cell
'
With ActiveCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Select Case .Value
Case Is < 1000
ActiveCell.NumberFormat = "0;-0;;@"
Case Else
ActiveCell.NumberFormat = "#,###"
End Select
End If
End If
End With
End Sub
Hope it helps
_LF
RE: combining number formats
One small problem. I am not smart enough to figure out where to put the code or how to execute it.
Can someone please explain that part?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: combining number formats
respects
ijr
RE: combining number formats
went to tools/macro/vbe.
Insert/module.
Cut pasted into the module:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'
' two number formats for a single cell
'
With ActiveCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Select Case .Value
Case Is < 1000
ActiveCell.NumberFormat = "0;-0;;@"
Case Else
ActiveCell.NumberFormat = "#,###"
End Select
End If
End If
End With
End Sub
Close and Return to excel.
How do I execute it?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: combining number formats
1 - Set all cells to number format #,###
2 - Format/Conditional formatting, If cell value = 0 , Then set set format font-color=white.
But if anyone can explain how to execute a subroutine above I would still be interested.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: combining number formats
I dropped by here to let you know of conditional formatting and to cancel out my earlier post on where your vba should be. Palusa told you actually that you should write your code in that specific sheet and he is right.
I keep functions in modules but I think subroutines are more sensitive to where you store them because they deal with more cells and they produce an action effecting a range of cells, while functions simply return a value.
For this particular sub, reference is made to the sheet you will process. So first keep in mind the name of the sheet you are storing data(say sheet1), then switch to VB window, in the folder area there(usually on the left) double click the "sheet1" and the code window(usually on the left) should read "worksheet",paste the code or write it on there. Then switch to excel window and type in numbers some less than 1000 and some in excess, and there will be some action everytime you move to a different cell. I tried it and it worked(though I dont know what the theme is).
respects
ijr
RE: combining number formats
apologize i was out of office & couldn't get your msg.
To activate the macro i gave you:
(1) open VB (alt F11);
(2) within the VBA project panel, locate your working file: normally you have a structure such as: "(-)VBA Project (MyProg.xls)"... and below... "(-)Microsoft Excel Obiects" ... and below "Sheet1 Sheet2 ... ThisWorkbook".
(3) Select the sheet where you want to apply this formatting by doubleclicking.
(4) In the code panel paste the routine i sent.
VoilĂ , you don't need to activate it: it will run in background because it traps any change in the worksheet (try it).
Of course if you intend to apply in other sheets of the same workbook repeat the above steps.
Hope it helps
_LF
RE: combining number formats
#,###;-#,###;;@
RE: combining number formats
Not sure whethre this is helpful, but two different formats for one cell I did once was a CUSTOM FORMAT entered like this
[>=1000]#,###;###.0
----------------------------------
Hope this helps.
----------------------------------
maybe only a drafter
but the best user at this company!