Delete special character

K

Kiannie

"I have this symbol " ' " in the entire sheet
ie :
'0122344
'0123234
'8384392

May i know how to delete all the " ' " , which is the the first character of
the above ?
bear in mind all r followed with ZERO.
 
F

Francis

do you want the Zero? if yes try this
=SUBSTITUTE(A2,"'"," ")
This will give you 0122344 but it is not a number

if you don't need the zero, simply use the Replace feature from
Edit in the Tool bar
type ' under Find what and click OK

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
F

Francis

Oops, sorry, there are typos

=SUBSTITUTE(A2,"'","")

there is no space for the last arugment

if you don't need the zero, simply use the Replace feature from
Edit in the Tool bar
type ' under Find what and click Replace All

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
S

Saruman

The apostrophe is a symbol that says the contents of the cell is text.
It is a non printing character and should not be a problem for you.
If it is removed, you will lose the leading zeros in front of some of the
numbers.
If you really want to get rid of it, try this:
Save the sheet first in case the result is not what you require
Type a number 1 in a blank cell
Copy the cell
Highlight the cells where you want to remove the apostrophe
Right Click and Paste Special
In the Operation part of the menu, halfway down, select Multiply
Click OK to finish
Text Numbers multiplied by 1 turns text numbers back into numbers
They are text numbers for a reason though, some formulas that reference them
may stop working when they are turned back into numbers.
Be Warned, try it on a copy of the file, keep the original.
 
Top