16 thoughts on “Formatting Job Numbers”

1. I found that handy feature many times before. I use Excel for engineering work, and I like to include units with my numbers (prevents a lot of confusion). The most common one I use is ° (the degree symbol). I just open up a custom format and type 0 Alt-0176. And voilà my angles have units, but are still numbers.

2. This not-so expert user needs help: How does your MAX formula work? I created two columns of data and named them JobNoList (numbers) and JobDescList (text), but can’t make the formula work.

3. Wayne: It’s an array formula so you have to enter with control+shift+enter, not just enter. See http://www.dicks-blog.com/archives/2004/04/05/anatomy-of-an-array-formula/ for more info on array formulae.

4. Ah. I had tried making it an array formula, but the mistake I made was I named the entire column (i.e. JobNoList = Sheet1!$A:$A) instead of naming a fixed-length array.

   Thanks.

5. A very neat thing that I’ve put to immediate use – thanks.

   But never happy with ‘almost what I want’, as far as I can tell, it’s not possible to format the custom formatting elements – eg to be bold.

   The help file doesn’t indicate formatting beyond the standards. And since the content of the cell remains unchanged (eg 1 in the above example), it’s not possible to use the .character(start, length) method in vba to format parts of the cell contents.

   I suppose you’re going to tell me that Excel is not really a layout program? :-)

   cheers,

6. Christopher –

   “And since the content of the cell remains unchanged (eg 1 in the above example), it’s not possible to use the .character(start, length) method in vba to format parts of the cell contents.”

   I don’t understand your point. Whether the contents of a cell change or not, you can still use VBA to format some of the characters differently than others. It’s not too elegant, of course, but it’s doable.

   – Jon

7. you can still use VBA …it’s not too elegant, of course, but it’s doable

   OK – it must be my ignorance showing :-)

   When I want to format parts of a cell with code, I use the characters object of a range. For example:

   Selection.Characters(1, 3).Font.Bold = True

   sets the first three characters in the selected cell to bold.

   But that approach won’t work in Dick’s formatting example – because the cell contains 1 character – not the 6 that the user sees due to the formatting.

   I couldn’t see from the help file how to manually format part of the formatting so it was bold – and I couldn’t figure out how to do it with code.

   For example, using the formatting above as an example, if I wanted to make everything after the hyphen bold (and I don’t know many characters occur after the hyphen), then following snippet is what I tried (and obviously failed)

   With Selection    sCellTextFormat = Format(.Value, .NumberFormat)    lDashPosition = InStr(sCellTextFormat, “-“)    lLengthChange = Len(sCellTextFormat) – lDashPosition        With .Characters(Start:=lDashPosition + 1, Length:=lLengthChange).Font        .Bold = True    End WithEnd With

   Any suggestions on what object I should be trying to manipulate would be appreciated.

   cheers,
   Christopher

8. hhmmm…that code snippet (using Juan’s VBE HTML maker add-in) didn’t come out right…let’s try again:

   With Selection    sCellTextFormat = Format(.Value, .NumberFormat)    lDashPosition = InStr(sCellTextFormat, “-“)    lLengthChange = Len(sCellTextFormat) – lDashPosition        With .Characters(Start:=lDashPosition + 1, Length:=lLengthChange).Font        .Bold = True    End WithEnd With

9. OK – just a straight copy/paste…(i feel bad coz I can’t edit my post…:-(

   With Selection
   sCellTextFormat = Format(.Value, .NumberFormat)
   lDashPosition = InStr(sCellTextFormat, “-“)
   lLengthChange = Len(sCellTextFormat) – lDashPosition

   With .Characters(Start:=lDashPosition + 1, Length:=lLengthChange).Font
   .Bold = True
   End With
   End With

10. “But that approach won’t work in Dick’s formatting example – because the cell contains 1 character – not the 6 that the user sees due to the formatting.”

    My bad. I didn’t look closely enough at Dick’s example.

    This is when things get either very complex or very simple. I am a big proponent of using multiple sheets: one for the data behind the workbook, one for a tabular display, one formatted to produce good charts, and any others you may need. The sheets are all somehow linked together.

    To maintain mixed character formatting in the display sheet, you’ll need to link cells in VBA, meaning you should launch your update values and update formats code from a worksheet_change kind of event procedure. The display cells are inert in terms of worksheet formulas.

    It’s messy, and you may just decide to dispense with the mixed character formatting.

11. I am a big proponent of using multiple sheets

    Yup – in most cases

    you may just decide to dispense with the mixed character formatting

    Yup – for the problem at hand. :-)

    thanks,
    Christopher

12. “I am a big proponent of using multiple sheets”

    Jon, do you have, or can you point to, any samples of this?

13. What happens when the you retrieve a 2005 job number in 2006?

14. I am looking to average a column of data, some of which are entered with the following format:

15. Even though this article was posted some time ago it is still very useful! Some additional information on the topic is available on the following website:

    http://www.navigatorpf.com/Current_Topics/Financial_Modelling_for_Project_Finance/Custom_Number_Formats_in_Excel/

16. Hi, I found this thread googling for a solution to my problem. It’s a bit off topic but perhaps you can help.

    I have something in my cell like:

    =”Reason for transfer: “&D5

    The D5 being a set of data of which you do not know the length nor the type of data. How would I go on about formatting this D5 bit bold?

Posting code? Use <pre> tags for VBA and <code> tags for inline.