Change 1/2 to .5

J

Jambruins

I have copied a bunch of data into a spreadsheet and column G has numbers in
it. Some of the numbers are in this format 5½, 6½, etc. Is there a way to
change the 5½ to 5.5 and 6½ to 6.5, etc.? Thanks.
 
C

CLR

Try this in a helper column and copied down................

=SUBSTITUTE(A1,CHAR(189),".5")*1

Vaya con Dios,
Chuck, CABGx3
 
J

Jambruins

that doesn't work. It still leaves the number with 1/2 next to it. Any
other ideas? Thanks
 
D

Dave O

Highlight the cell in question, right click, select Format Cells and
change the format from Fraction to Number with the appropriate number
of decimal places.
 
J

Jambruins

CLR and Dave, they did not work either. I used the paste special command and
pasted the numbers in in html format. Would that be the problem? Thanks
 
C

CLR

You might go get Chip Pearson's "CellView" Add-in and install it to find out
exactly what characters are in the cell, or use the "CODE()"
function.......... then modify the SUBSTITUTE formula to replace it, or
perhaps use Edit > Replace to replace it.....

Vaya con Dios,
Chuck, CABGx3
 
J

Jambruins

When I use the "code()" function it gives me 49 for 1½, 50 for 2½, etc. I
guess the replace command will work best here unless you have any other
ideas. Thanks
 
C

CLR

Thats a phoney reading based on both characters being in the
cell.........delete the integer and just look at the "1/2" character to see
what it is........

"Find and Replace" is good, but gets sticky with problems like this.....

Vaya con Dios,
Chuck, CABGx3
 
J

Jambruins

actually the substitute formula gives me a #VALUE! error. Disregard my last
post about it giving me 1-Nov.
 
J

Jambruins

that gives me 189. I plugged in the substitute formula below and it gave me
1-Nov. I tried formatting each cell but nothing worked. Any other ideas?
Thanks
 
J

Jambruins

nevermind, the substitute formula works great. Sorry for all the confusion
and thanks for all the help.
 
C

CLR

You're welcome, glad you got it working, and thanks for the feedback...........

Vaya con Dios,
Chuck, CABGx3
 
Top