how do i get rid of hidden apostrophes in cells?

[Guest]

How do I get rid of all the hidden apostrophes poplating every cell with
data, text and numeric?

 

[Alan]

Try Tools > Edit > Replace ' > Replace With > leave bank > Replace All
Regards,

 

[Guest]

When I tried REplace to try and answer this question, Excel didn't find the
Lead '.
If you are having the same issue, brokenmonkey, you might try:
One way to fix a column like this:
Create an extra column.
Put this formula in it: =LEFT(A1,LEN(A1))
Copy it down.
Then Copy all the cells and Edit | Paste Special, Values.
Last Delete the original column.

tj

 

[RagDyer]

OR
Just select the column with the leading, hidden apostrophes, and simply open
and close "TextToColumns".

Select the column.
<Data> <TextToColumns> <Finish>
And you're done!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


When I tried REplace to try and answer this question, Excel didn't find the
Lead '.
If you are having the same issue, brokenmonkey, you might try:
One way to fix a column like this:
Create an extra column.
Put this formula in it: =LEFT(A1,LEN(A1))
Copy it down.
Then Copy all the cells and Edit | Paste Special, Values.
Last Delete the original column.

tj

 

[Guest]

You must be a Monarch user.

If you use Excel 2k (like I do), try either one of these two macro's . They
both work for me. I hear Excel 2002 has a tool to do this built in.



Sub a_ApostroRemove1()
'
'
'Removes hidden apostrophes that are first characters.
'Works on cells with formulas, text, or values.

For Each currentCell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
If currentCell.HasFormula = False Then
'Verifies that procedure does not change the
'cell with the active formula so that it contains
'only the value.
currentCell.Formula = currentCell.Value
End If
Next
End Sub



Sub a_ApostroRemove2()
'
'
'Removes hidden apostrophes that are first characters.
'Public Sub ApostroRemove()

Dim currentCell As Range
On Error Resume Next
For Each currentCell In Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With currentCell
.Formula = .Value
End With
Next