Hiding the leading apostrophes after copying data to excel

[akashsen]

Hello,

Whenever I paste some data having leading apostrophes to Excel worksheet from a clipboard or after copying it from notepad, the leading apostrophe doesn't get hidden. Any solution available ?

 

[akashsen]

Hello,

Whenever I paste some data having leading apostrophes to Excel worksheet from a clipboard or after copying it from notepad, the leading apostrophe doesn't get hidden. Any solution available ?

Also when I double click any cell which has a leading apostrophe visible, they get hidden, but since I have huge amount of data copied I can't do this operation for all the cells.

 

[anglais428]

If the apostrohes are always in the same position you could use Text to Columns. Go to Data tab, text to columns, delimit, select other and put in ', then finish. This should move all the other text into a the next cell.

If there are no other apostrohes elsewhere in the cell range then you could highlight the range, click CTRL + H, and replace ' with blank, replace all. This would remove all the apostrophies in the range, so it wouldn't be the best option if you ONLY want to remove the leading ones.

 

[akashsen]

If the apostrohes are always in the same position you could use Text to Columns. Go to Data tab, text to columns, delimit, select other and put in ', then finish. This should move all the other text into a the next cell.

If there are no other apostrohes elsewhere in the cell range then you could highlight the range, click CTRL + H, and replace ' with blank, replace all. This would remove all the apostrophies in the range, so it wouldn't be the best option if you ONLY want to remove the leading ones.

Thnx for the quick reply.. but the problem with your solution is that it deletes any leading zeroes that I have in a cell, which I need to preserve.

 

[anglais428]

can you provide an example. eg is it **000 in each cell?

Try =SUBSTITUTE(cell,"*","")

 

[akashsen]

<table style="width: 419px; height: 212px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 93pt;" width="124"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 93pt;" width="124" height="20">Thnx a lot, Substitute function worked great...
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td> </tr> </tbody></table>