Is there a way to change 126½

[jcaptchaos2]

Is there a way to change 126½ which is text to 126.5 as a number?

 

[GTO]

If you look at the cell's formatting, you may find that the number format is already set to Fraction, in which case, just change to Number.

Does that helps?

Mark

 

[jcaptchaos2]

The 126½ is text, so changing the format does not help.

 

[GTO]

How does the "126 1/2" get into the cell? When I type it in, Excel automatically changes the cell's number formatting to Fraction.

 

[lenze]

Place a 1 in a blank cell and copy. Now select your cells with the TEXT fractions and choose Paste Special>Multiply

lenze

 

[jcaptchaos2]

Lenze, that does not seem to change anything

GTO - The 126½ in pasted into the cell from a website. When you select the cell it appears as '126½

 

[jcaptchaos2]

Is there a way that I could pull all numbers to the left of the ½ from the 126½. Keep in mind that the numbers could be 6½ so the formula =Left(cell,3) won't work.

 

[tony80]

Try =Left(cell,Len(cell)-1)

 

[xenou]

Taking Tony's idea one step further, maybe we can work in the decimal values - I think its the right idea that we are dealing with single characters so I'm guessing ascii 188-190 are the fractional symbols (just ¼, ½, ¾)

=LEFT(A1,LEN(A1)-1)+LOOKUP(CODE(RIGHT(A1,1)),{0,188,189,190,65536},{0,0.25,0.5,0.75,0})

But since it would fail on a single character (1-9),
=IF(LEN(A1)<2,A1+0,LEFT(A1,LEN(A1)-1)+LOOKUP(CODE(RIGHT(A1,1)),{0,188,189,190,65536},{0,0.25,0.5,0.75,0}))

¿Perhaps someone can find a more elegant formula? Edit: note, also fails if the cell value is just ¼, ½, or ¾ by itself

 

[mgirvin]

Maybe this:

=CLEAN(Left(cell,Len(cell)-1))+0


CLEAN would remove non-printing characters; +0 convert to number.

Maybe a TRIM too if there are spaces:

=TRIM(CLEAN(Left(cell,Len(cell)-1))+0)