how do i get rid of hidden apostrophes in cells?
 

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

Try Tools Edit Replace ' Replace With leave bank Replace All
Regards,
"brokenmonkey" wrote in message
...
How do I get rid of all the hidden apostrophes poplating every cell with
data, text and numeric?

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

"Alan" wrote:

Try Tools Edit Replace ' Replace With leave bank Replace All
Regards,
"brokenmonkey" wrote in message
...
How do I get rid of all the hidden apostrophes poplating every cell with
data, text and numeric?

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!
==============================================


"tjtjjtjt" wrote in message
...
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

"Alan" wrote:

Try Tools Edit Replace ' Replace With leave bank Replace All
Regards,
"brokenmonkey" wrote in message
...
How do I get rid of all the hidden apostrophes poplating every cell with
data, text and numeric?

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(xlCellTypeConst ants)
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
On Error GoTo 0
End Sub



"brokenmonkey" wrote:

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