# Is there a way to change 126½

##### Well-known Member
Is there a way to change 126½ which is text to 126.5 as a number?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### GTO

##### MrExcel MVP
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

##### Well-known Member
The 126½ is text, so changing the format does not help.

#### GTO

##### MrExcel MVP
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

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

lenze

##### Well-known Member
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½

##### Well-known Member
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

##### New Member
Try =Left(cell,Len(cell)-1)

#### xenou

##### MrExcel MVP
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

##### Well-known Member
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)

Replies
1
Views
122
Replies
10
Views
378
Replies
1
Views
89
Replies
3
Views
143
Replies
1
Views
1K

1,191,189
Messages
5,985,197
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back