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.

Hi,

Change the cell formating to Number (right click on the cell and select
Format cells, in Number tab select Number under Category).

Hope this helps.
--
Best regards,
Peter Jaušovec
http://blog.jausovec.net
http://office.jausovec.net


"Jambruins" je napisal:

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.

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.

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

"Peter Jausovec" wrote:

Hi,

Change the cell formating to Number (right click on the cell and select
Format cells, in Number tab select Number under Category).

Hope this helps.
--
Best regards,
Peter Jaušovec
http://blog.jausovec.net
http://office.jausovec.net


"Jambruins" je napisal:

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.

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.

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

"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.

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


"Jambruins" wrote:

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

"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.

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

"CLR" wrote:


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


"Jambruins" wrote:

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

"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.

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



"Jambruins" wrote:

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

"CLR" wrote:


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


"Jambruins" wrote:

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

"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.

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

"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.

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

"CLR" wrote:

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



"Jambruins" wrote:

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

"CLR" wrote:


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


"Jambruins" wrote:

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

"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.

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

"CLR" wrote:

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



"Jambruins" wrote:

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

"CLR" wrote:


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


"Jambruins" wrote:

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

"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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

"CLR" wrote:

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



"Jambruins" wrote:

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

"CLR" wrote:


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


"Jambruins" wrote:

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

"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jambruins" wrote:

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.