Is there a way to change 126½

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,032
Office Version
  1. 365
Platform
  1. Windows
Is there a way to change 126½ which is text to 126.5 as a number?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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.
 
Upvote 0
Place a 1 in a blank cell and copy. Now select your cells with the TEXT fractions and choose Paste Special>Multiply

lenze
 
Upvote 0
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½
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top