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