How to Convert Decimals to Feet, Inches and Fractions

Hello Everyone,

I've searched high and low and could not find an answer to my question. I'd like to know if it is possible to convert numbers with or without decimal points into the folliowing different formats within the same cell depending on 3 possible entry formats. I don't need to use these numbers for any further calculation so no worries there.

Example of possible entry formats into the same cell:

1.) If I enter 17.2.5 into the cell, the cell need to be converted to 17' 2.5"
2.) If I enter 17.2 into the cell, the cell need to be converted to 17' 2"
3.) If I enter 17 into the cell, the cell need to be converted to 17'

Ideally I'd like to accomplish this using custom number formatting, not VBA.

Thank you in advance for your guidance.

This sort of thing is not going to be accomplished by number formatting. Something like "17.2.5" is a text string, not a number, so number formatting cannot change its appearance. In the end, the only way to do this is to use string manipulation functions to read the input text string and manipulate it into the desired output text string. In order to do this in a single cell, you will need VBA (probably a change event) to take the input and do the manipulation and output the result back to the same cell.

At this point, I suggest that you look at your requirements and decide what you must have, and what requirements you can relax:
1) Text input with pattern.
2) Manipulate text to desired output pattern.
3) All in one cell.
4) Without using VBA.

For example, if you relax the "all in one cell" requirement, this should be relatively easy to accomplish using one cell as input and a second cell containing a suitable combination text manipulation formulas (SUBSTITUTE() maybe?) as output.

Yes, a VBA procedure will allow you to read the input from the cell, process the text string, then output the result back into the same cell. What part of building and implementing this procedure do you need help with? I would expect something like this (quickly put together so it will need more development):

Please Login or Register  to view this content.

If you want this to occur automatically whenever something is entered into a cell (or a specific cell), you will need to put that inside of a change event procedure. There should be lots of examples of change event procedures on this forum and across the internet. Let us know if you have trouble finding and understanding them.

Change Event code: for data entered in column 1

Please Login or Register  to view this content.

Enter data in column 1

To add code: right click on tab, "View Code", then copy/past code.

===============================================================

Function for data in any column:

With data in C1, then in D1

=dec2imp(C1)

Please Login or Register  to view this content.