×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

combining number formats
2

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)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: combining number formats

Pete:
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

Try this (code shall be in the specific sheet):

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

(OP)
Thanks Palusa.  I think that is exactly what I need.

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

That is a very important question electricpete. I usually put my codes in a "module". Putting them anywhere else gets me into a maze of problems(problems here means no problem since there is simply no action)

respects
ijr

RE: combining number formats

(OP)
OK. I know where to insert it as follows:

   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

(OP)
I think I have figured out how to accomplish this without vba code.

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

Yeah electricpetet

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

Hi electricpete,
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

I apologize if I have completely missed your point, but have you tried this format:?

#,###;-#,###;;@

RE: combining number formats

Hello,

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources