I can't figure out how to do custom formatting that would format input "1/5" as "1 year, 5 months", or "5,0" as "5 years". I do want the cell to retain original input of "1/5" but visually display "1 year, 5 months".
Firstly, this solution is flawed in the sense that it requires leading 0's for months and will only take input as yymm (without the / or ,), also it will not change the pluralization of years/months based on the number you have entered. Neither will it omit years or months if only one is entered.
Without further adiou however, here is my solution;
First select the cell(s) you wish to format. Right-click and select "Format Cells...". On the 'Number' tab select custom as your category. Type the string below into the 'type' field.
You can change the quoted strings to anything you wish, as long as you retain the quotation marks.
To test, enter a few tests;
This has been tested in Excel 2010 and 2003.
You could use a formula along these lines which splits the input based on the position for the
assuming that a text value such as