how to find and replace ' (char(39) symbol?

[bharath]

Hi,

How to find the special characters like char (39) or ' symbols.
for example i have '123456 by using find formula i could not find.
please help me

thank u for ur time

 

[Khalid NGO]

Hi Bharath,

If your string is in cell A1, what number you are getting with this formula:
=CODE(LEFT(A1))

39 or 49? If it is 49 that means excel is ignoring the first ' char.

It will help if you post a sample file with more example and explain what you want to achieve.

Regards,

 

[bharath]

Hi Bharath,

If your string is in cell A1, what number you are getting with this formula:
=CODE(LEFT(A1))

39 or 49? If it is 49 that means excel is ignoring the first ' char.

It will help if you post a sample file with more example and explain what you want to achieve.

Regards,

thank you somuch

 

[bharath]

hi,
i uploaded file

 

[Khalid NGO]

Hi,

Use Clean function in any other column say H3:
=CLEAN(F3)

Copy down...

Regards,

 

[jindon]

If you want to delete the apostrophe in the beggining of the cell, just select the column and go to [TextToColumns] then hit [Finish]

 

[bharath]

Hi,

Use Clean function in any other column say H3:
=CLEAN(F3)

Copy down...

Regards,

Hi,
Actually i want to use find and replace option(ctrl+H) for this.
But i am not getting....

 

[Khalid NGO]

apostrophe is not really a part of the cell's contents; that is why you can't use Find and Replace to find and replace it.

The apostrophe is actually considered a "prefix character" for a cell.

More details here:
http://excel.tips.net/T003332_Searching_for_Leading_Apostrophes.html

Regards,

 

[Chihiro]

You can't use CTRL+H in this instance, as Excel will ignore CHAR(49).
Did some test and even with Text to Col. It will have issue on cell F5. Though it won't matter much as it's text.

Easiest way to get rid of them I find, is to copy and paste to text editor of your choice and copy them back into Excel.