I use a custom format to display job numbers in the format YY-NNN, where Y is the year and N is an incrementing number. The first five jobs for this year look like this:
Note, however, that the formula bar shows the number 1, not the text displayed in the cell. The custom number format looks like this:
There are some advantages to having identifying numbers be actual numbers instead of text. For instance, I use a formula that finds the largest job number that has a description.
=MAX((JobNoList)*(LEN(JobDescList)>0))
It’s a lot easier to do that with numerics because MAX would return zero if all the job numbers were text. For more information on custom formatting codes, see Create a Custom Number Format in help.
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.
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.
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.
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.
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,
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
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
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
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
“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.
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
“I am a big proponent of using multiple sheets”
Jon, do you have, or can you point to, any samples of this?
What happens when the you retrieve a 2005 job number in 2006?
I am looking to average a column of data, some of which are entered with the following format:
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/
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?