Raj,

I'm till not sure what your target cell should look like but here are two
ideas:

Remove the single quote, retain the = but accept #NAME errors:
Sub Cleancolumn1()
For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells _
(Rows.Count, 14).End(xlUp).Row
Cells(i, 14) = Cells(i, 14).Value
Next i
End Sub


Remove the single quote, retain =, display without name error:
This means for a string like '123 yiu will end up with ="123"
I have no idea if this is acceptable.

Sub Cleancolumn2()
Dim A As String
Dim B As Long
For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells _
(Rows.Count, 14).End(xlUp).Row
A = Cells(i, 14)
B = Len(A)
Select Case B
Case 0
'do nothing
Case 1
'paste whatever is there
Cells(i, 14) = Cells(i, 14).Value
Case Is = 2
If Left(Cells(i, 14), 1) = "=" Then
A = Right(Cells(i, 14), B - 1)
ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = "=" & A
ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = "=" & Chr(34) & A & Chr(34)
Else
Cells(i, 14) = Cells(i, 14).Value
End If
End Select
Next i
End Sub





"Raj" wrote in message
...
I want to remove the apostrophe but retain the =

Regards,
Raj


On Mar 26, 8:30 pm, "Project Mangler" wrote:
Raj,

I see the point of the single quote.

If you remove it and paste the truncated string back into the cell you
will
get the name error again? Is this what you are trying to achieve or should
you remove the = as well?

DB

"Raj" wrote in message

...
Maybe this information will throw light on the underlying problem and
also help me with a solution:

Column 14 which is being cleaned has some cells beginning with an
apostrophe and an = sign. eg. '=KKRRNN
This has been done obviously because without the apostrophe Excel
treats the string as a formula and shows a Name error because it does
not understand the gibberish following the equal to sign.
The code was written to remove the apostrophe where one existed. I am
wondering whether the problem is occurring because string without the
apostrophe becomes a formula.

Please examine and also let me know any other way to get rid of the
leading apostrophe in a string in a cell.

Thanks in Advance.

Regards,
Raj

On Mar 26, 3:54 pm, "Project Mangler" wrote:

Raj,

I can't duplicate your error here, the code works OK if I try to detect
a
haracter other than "'" .

I'm wondering how you are going to detect a single quote at the start of
a
string? e.g.

If Left(Cells(i, 4), 1) = "'" Then
MsgBox "Apostrophe!"
Else
MsgBox "No Apostrophe!"
End If

The above line copied from your post gives me No Apostrophe.

In a cell containing the string 'Length I get a Len() of 6.

DB

"Raj" wrote in message

...

Hi,

When I run the following code, I am getting the Application or object
defined error. The second line is a single line in my code. The VBE
highlights the portion after "Then" in the second line.

Sub Cleancolumn1()
For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count ,
14).End(xlUp).Row
If Left(Cells(i, 14), 1) = "'" Then
ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14),
Len(Cells(i, 14)) - 1)
Next i
End Sub

What is going wrong?

Thanks in advance for the help.

Regards,
Raj