Replacing apostrophes in Excel?

[Syringer]

So I have a huge column of data with ' marks in the beginning, so they don't show in the actual cell..and I want to remove them. However if I do a replace it doesn't say it exists. How can I get rid of it?

 

[xtknight]

Weird. Are you sure they aren't tildes? ` <--

You could try replacing '' or \' instead of just ' also. But, you shouldn't have to do that with Excel.

 

[Syringer]

Yeah it's definitely the apostrophe you use when you don't want Excel to automatically format it..

 

[bruceb]

What happens if you select the column of data with the apostrophes and try using another number format ?

You may also need to create a custom format for that group of cells if a built in format is not correct for what
you want to do.

 

[esun]

Originally posted by: xtknight
Weird. Are you sure they aren't tildes? ` <--

You could try replacing '' or \' instead of just ' also. But, you shouldn't have to do that with Excel.

An aside, ` is not a tilde. ~ is a tilde. ` is a backquote, or backtick (or a grave accent linguistically speaking).

 

[PowerEngineer]

Hmmmm... I'm not sure they are still there.

I didn't realize that a first apostrophe would disappear like that. When I put "'s" in a cell, it appears as just "s" on the screen, and also acts like it. Len(Cell) = 1 and when I put "X" & Cell in another cell, it becomes "Xs". Putting "''s" in a cell gives you "'s".

Interesting...

It appears that the openning apostrophe tells Excel to input the rest of the characters as a string rather than a number.

 

[Syringer]

Yeah..what the apostrophe does is remove any automatic formatting within that cell. Try putting 3/17 in there, and by default it'll change to 3/17/2007 let's say..or put =3+5 and it'll change to 8. Putting a ' in front of either will leave it as it is.

 

[davenyl]

If its the whole column, you can select the column, copy and then paste special values into a new column. Then select the original column and clear contents. Copy the new column and then paste (full paste, not just values this time) into the original column.

 

[jamesave]

Originally posted by: davenyl
If its the whole column, you can select the column, copy and then paste special values into a new column. Then select the original column and clear contents. Copy the new column and then paste (full paste, not just values this time) into the original column.

Or you can use Data > Text To Column function.