Hello and welcome back to the Excel Tip of the Week! This week we have a General User post in which we take a look at how to use various formulas to format text, including formatting formula-derived numbers which are embedded in text. This Tip will touch on some formulas originally covered in TOTW #10 (UPPER, LOWER, PROPER), as well as using the custom formatting language that we discussed in TOTW #59. UPPER, LOWER, PROPER These are simple case-changing formulas that alter the capitalisation of words. They're useful for 'cleaning up' input data. UPPER changes to all-upper case letters and LOWER changes to all lower-case. Numbers and punctuation are unaffected. The PROPER function changes to 'proper case' - i.e. capitalisation for proper nouns. This means that the first letter of each word will be capitalised, and the remainder will be lower-case. This is most useful for cleanly formatting people's names, although of course it will fail on non-standard capitalisation names such as o'Connor or MacDonald. Company names are even more likely to use non-standard capitalisation (such as initialisms that don't use full stops) and hence PROPER is rarely appropriate. These formulas are all extremely simple to write: =UPPER(reference of original text) =LOWER(reference of original text) =PROPER(reference of original text) TEXT This function forces a string of text into a given text format. These formats are encoded the same way as you may have seen in earlier Tips or in the Format Cells menu. See these examples for a refresher: The formula itself goes like this: =TEXT(reference of original text, desired format code AS TEXT) Note that the desired format has to be entered as text - i.e. between quotation marks. So for example to convert an unformatted date in cell A1 to ISO format we would write: =TEXT(A1,"yyyy-mm-dd") If you use the CONCATENATE or & functions to join cell references together with text, you may have found that any values / dates pulled from cells always appear in General format, leading to nonsense like "The year end date is 42369". Instead we could use a formula such as: ="The year end date is "&TEXT(A1,"yyyy-mm-dd") To get: The year end date is 2015-12-31 Previous post - Further dropdown menu approaches TOTW index Next post - Improving workbook viewing This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.
I did write something on this back in 2010 for the Excel community :
Also, more recently on AccountingWeb.
I think Excel rounds -0.5 to -1 so the second condition needs to be
I think, because conditional custom number formats work differently from normal ones, the dash for the minus is still there for the third format section. With the question mark I guess that the dash overwrites the minus, but this doesn't seem to be the case for the underscore. Someone else might have a more complete explanation.
This site has some useful information about number formatting
The section on 'Changing the Default Number Format Conditions' is particularly interesting and allows you to use something like the following so that 0.2 shows as - rather than 0:
I'm pretty sure that this has been mentioned on here before (by Simon, I think), but I've not been able to find the post again.
One thing that I don't understand though...
Why does this work: [>=0.5]#,##0_);[
But this gives a double dash for -0.3: [>=0.5]#,##0_);[
Many thanks for the mention Alan. You could also use the third section of the custom number format to replace zeroes with dashes:
The question mark at the end just adds a space so that the dash isn't right over at the right-hand side of the cell.
For those of us who like to display negative numbers both in red and in "round brackets", the following format is useful when a column contains both positive and negative numbers whose decimal points you would like to keep aligned:
This is especially useful if the data is to be printed both by people with a colour printer (who can easily spot the negatives as they are red) and people printing in black and white who can more easily identify the negatives by the brackets than by the small "-" in front of the number.
PS Thanks to Simon for this!