How do I display a number as both a decimal and a fraction?

I would like to somehow create a cell format or macro\VBA where when I type in my dimension in inches into the cell, such as 1.75, that is displays BOTH the fraction and decimal.

Example. Type 1.75 into cell A1 hit enter
A1 Displays: 1 3/4" (1.75")

I'd like the fraction to show first and the decimal to show in parenthesis ( ). The cell format for the first step would be: # ??/??\", but I am not sure how to get it to read the second part with the decimals.

Anyone know how to do this? I am not familiar with coding, macros or VBA, so any step by step instructions would be extremely helpful.

I am really good with formulas and embedding multiple formulas into one another, but I cant seem to find anything that will work for this kind of scenario, which is why I am thinking of a macro or VBA.

Thank you
Debbie

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

Since you're new here I'll change then title for you but not for the future.

Check out this post by shg for a potential solution. It's a good start toward a solution. Here's a modified version that adds the decimal portion in parentheses:

Please Login or Register  to view this content.

Please note this is relatively untested. All credit to shg for the formula.

Edit: Did not understand that this should be a single cell solution. Leaving formula in case it's useful.

Hi,

You can Format a cell like 1.75 as a Fraction and see it presented as 1 3/4
I don't believe you can format it to show BOTH the original decimal and a fraction. You'd have to have the decimal version in another cell pointing to the original number and formatted as a number.

the other thing I would note is that shg's formula results in a text string -- which is not exactly the same as a formatted number.

Does it have to be in the same cell? The way I might do this is to enter 1.75 in one cell (A1). The adjacent cell immediately to the right (B1) could be =A1. A1 is formatted as ? ??/?? and B1 is formatted as (General) and left aligned (maybe other cell formatting), so the two values are clearly together.

It is up to you how important it is to keep the number as a number or to convert to a text string.

A formula would require another cell. I don't think there's any way to show the same value formatted multiple ways in the same cell.

I agree with Melvosh -- there is no way to have both a formula and enter values into a cell.

If I were in this situation, I would probably have a helper range (maybe even in a separate file if I was not allowed to use a range in the template file). I would enter my values in the left column and have that formula in the right column. After entering my values, then I would select the second column -> copy -> select the column in the template file -> paste special -> as values. Be sure to remember that this converts the number 1.75 to the text string "1 3/4 (1.75)" which is not usable in other calculations. =2*"1 3/4 (1.75)" will return an error because Excel does not know how to use that text string as a number. I think this is how I would do it if I was not allowed to make any changes to the file (other than data entry and number formatting).

If you are not allowed to so much as add a column to the template file, then you probably also cannot add a change event macro. For completeness' sake, I will mention that one could conceivably have a change event macro that will detect when you enter a value in this column -- read the value entered -- convert to the desired text string -- write the text back into the modified cell. Again, if you are not allowed to add any columns, I doubt you are allowed to add a macro to the file, either. Just in case -- something like that can work.

I could see this becoming very tedious in the long run. I might talk to the author/controller of the file and lobby them to either make some changes to the file or allow you to make a few changes. If they just won't budge, I think I would keep a small file on hand with shg's formula to use as a data entry file, then copy and paste as values into the real file.

This tutorial talks about change event procedures: https://www.excelanytime.com/excel/i...=79&Itemid=475 If you work your way through the tutorial, you should get to their "Error Handler example 1" and "error handler example 2" examples -- which are pretty close to what I would expect for your code. In their examples, the code checks if A1 or B1 are the changed cell (if yes then exit). If any other cell in column 1 or 2 is changed and the value entered is a number, then it will add 1 to B2. In your case, instead of adding 1 to B2, you will be reading the changed value, converting it to the text string as noted above, then writing that back into the changed cell.

Please Login or Register  to view this content.

When you decide to implement the unit conversion, that should also fit in the same skeleton -- instead of just concatenating the two formats, your code will need to perform the unit conversion then concatenate the results (or whatever your desired output format will be). There are a lot of change event examples on this forum and around the internet. Most of them use a skeleton very similar to the one in this tutorial. If you are allowed to add a macro to the file, that may be the easiest way to simplify your data entry.

VBA Format function help file: https://docs.microsoft.com/en-us/off...r-applications

	A	B	C
1	Value	Presentation
2	1.3125	1.3125 (1 5/16)	B2: =A2 & TEXT(A2, " (? ??/??)")
3	2.25	2.25 (2 1/4 )
4	2.625	2.625 (2 5/8 )
5	3.75	3.75 (3 3/4 )
6	4.625	4.625 (4 5/8 )