How to convert fraction format to text format ......

[Epinn]

....... and still shows the fraction instead of decimals?

I have formatted a range of cells to fraction. Then I key in the following values:-

1/2
1/5
1/4
1/3
2/7
9/16 etc. etc.

Then I format the cells to text and the fractions are converted to decimals. How can I have both text format and the fractions displayed as above without writing a formula?

I tried custom format, paste special>values etc. and had no luck.

I am also having trouble with a formula which works most of the time but not *all the time*.

e.g. A7 formatted to fraction>8/16 then I key in 11/16.

The formula =TEXT(A7,"##/##") returns 9/13 instead of 11/16.

If I have to use a formula, is there a way to write one that will take care of all types of fractions shown in the drop-down list e.g. 1/4, 21/25, 8/16, 1/2, 3/10 etc. etc.

Appreciate guidance.

Epinn

 

[Sandy Mann]

Epinn,

When you enter the 1/2, 1/5 etc. you will see that in the formula bar it
shows 0.5, 0.2 etc. which is what is the value that is really held in the
cell not what you see, (which of course is only displayed differently to
show it how you requested. So when you reformat it to Text it is
reformatting the real value so it is not actually changing anything so does
not require a formula.

As to your text formula problem, I get 11/16 as expected.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


....... and still shows the fraction instead of decimals?

I have formatted a range of cells to fraction. Then I key in the following
values:-

1/2
1/5
1/4
1/3
2/7
9/16 etc. etc.

Then I format the cells to text and the fractions are converted to decimals.
How can I have both text format and the fractions displayed as above without
writing a formula?

I tried custom format, paste special>values etc. and had no luck.

I am also having trouble with a formula which works most of the time but not
*all the time*.

e.g. A7 formatted to fraction>8/16 then I key in 11/16.

The formula =TEXT(A7,"##/##") returns 9/13 instead of 11/16.

If I have to use a formula, is there a way to write one that will take care
of all types of fractions shown in the drop-down list e.g. 1/4, 21/25, 8/16,
1/2, 3/10 etc. etc.

Appreciate guidance.

Epinn

 

[Epinn]

Sandy,

I understand your first paragraph completely before I posted.

After I posted, I made a "discovery" which is probably nothing to the experts. I am going to start a new thread.

With my "discovery" I will approach this fraction issue differently. Originally, I thought formatting the cell to TEXT would make it TEXT. Now, my discovery told me otherwise, so I can solve my problem by just clicking the left align button. This way I don't see the decimals.

I do need the formula TEXT( ) if I do want a true TEXT format.

I think I got a "dirty" (in the English sense not Excel talk) cell, when I didn't get 11/16 using the formula. The decimal value wasn't 0.6875; it was three times as long. When I started fresh, I was okay.

I'll leave the following unanswered question for another day.

"How can I have both text format and the fractions displayed .......... without
writing a formula?"

I have to start my new thread now.

Thanks for dropping by.

Epinn

Epinn,

When you enter the 1/2, 1/5 etc. you will see that in the formula bar it
shows 0.5, 0.2 etc. which is what is the value that is really held in the
cell not what you see, (which of course is only displayed differently to
show it how you requested. So when you reformat it to Text it is
reformatting the real value so it is not actually changing anything so does
not require a formula.

As to your text formula problem, I get 11/16 as expected.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


....... and still shows the fraction instead of decimals?

I have formatted a range of cells to fraction. Then I key in the following
values:-

1/2
1/5
1/4
1/3
2/7
9/16 etc. etc.

Then I format the cells to text and the fractions are converted to decimals.
How can I have both text format and the fractions displayed as above without
writing a formula?

I tried custom format, paste special>values etc. and had no luck.

I am also having trouble with a formula which works most of the time but not
*all the time*.

e.g. A7 formatted to fraction>8/16 then I key in 11/16.

The formula =TEXT(A7,"##/##") returns 9/13 instead of 11/16.

If I have to use a formula, is there a way to write one that will take care
of all types of fractions shown in the drop-down list e.g. 1/4, 21/25, 8/16,
1/2, 3/10 etc. etc.

Appreciate guidance.

Epinn

 

[Epinn]

You are invited to my new thread in the *functions* forum.
Format>Cells>Number>Text is very tricky!

Epinn

Sandy,

I understand your first paragraph completely before I posted.

After I posted, I made a "discovery" which is probably nothing to the experts. I am going to start a new thread.

With my "discovery" I will approach this fraction issue differently. Originally, I thought formatting the cell to TEXT would make it TEXT. Now, my discovery told me otherwise, so I can solve my problem by just clicking the left align button. This way I don't see the decimals.

I do need the formula TEXT( ) if I do want a true TEXT format.

I think I got a "dirty" (in the English sense not Excel talk) cell, when I didn't get 11/16 using the formula. The decimal value wasn't 0.6875; it was three times as long. When I started fresh, I was okay.

I'll leave the following unanswered question for another day.

"How can I have both text format and the fractions displayed .......... without
writing a formula?"

I have to start my new thread now.

Thanks for dropping by.

Epinn

Epinn,

When you enter the 1/2, 1/5 etc. you will see that in the formula bar it
shows 0.5, 0.2 etc. which is what is the value that is really held in the
cell not what you see, (which of course is only displayed differently to
show it how you requested. So when you reformat it to Text it is
reformatting the real value so it is not actually changing anything so does
not require a formula.

As to your text formula problem, I get 11/16 as expected.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


....... and still shows the fraction instead of decimals?

I have formatted a range of cells to fraction. Then I key in the following
values:-

1/2
1/5
1/4
1/3
2/7
9/16 etc. etc.

Then I format the cells to text and the fractions are converted to decimals.
How can I have both text format and the fractions displayed as above without
writing a formula?

I tried custom format, paste special>values etc. and had no luck.

I am also having trouble with a formula which works most of the time but not
*all the time*.

e.g. A7 formatted to fraction>8/16 then I key in 11/16.

The formula =TEXT(A7,"##/##") returns 9/13 instead of 11/16.

If I have to use a formula, is there a way to write one that will take care
of all types of fractions shown in the drop-down list e.g. 1/4, 21/25, 8/16,
1/2, 3/10 etc. etc.

Appreciate guidance.

Epinn