I want to remove the apostrophe but retain the =
Regards,
Raj
On Mar 26, 8:30 pm, "Project Mangler" <dbl...@ntlworld.com> 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" <rsp...@gmail.com> wrote in message
>
> news:f558f9d9-a4e6-46de-ad73-f9c37755a786@x11g2000prb.googlegroups.com...
> 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" <dbl...@ntlworld.com> 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" <rsp...@gmail.com> wrote in message
>
> >news:9aab4b9b-9698-4667-84cf-6dda14d15489@g1g2000pre.googlegroups.com...
>
> > > 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