easy one???

[RICHMATERIALS]

Ladies and Gents,
I have a column of six digit numbers, each of which are preceded with a single lower case "o". I would like to replace the "o" in each value with a numeric "0", on the entire column at once using find/replace.

I have formatted column to text, but to no avail - result redacts the preceding zero.

Thanks
Rich

 

[Peter_SSs]

Rich

Try formatting the cells as "Custom" with the Type: 0000000
before doing the Find/Replace

 

[pgc01]

Hi Rich

Replace the o by '0

The single quote will not be part of the cell's value, it's just a prefix that tells excel that the value is text and not a number.

 

[VoG]

Try

=SUBSTITUTE(A1,"O",0,1)

filled down.

 

[RICHMATERIALS]

Thank you Peter_SSs

Faster than a speeding kangaroo, my friend! So fast, in fact, that the server hasn't yet updated to include our thread. Your solution worked just fine - appreciated.

 

[RICHMATERIALS]

Thank you, sir.

 

[RICHMATERIALS]

Thanks - amazing the sheer variety of correct answers - is Excel a microcosm of life?