How do I remove leading apostrophes in Excel?

I have a large Excel spreadsheet made up of about 30,000 cells.

Most of them have a leading apostrophe (') in them, whether they are numbers
or text.

I would like to remove all the leading apostrophes without having to do it
manually, cell by cell.

Any ideas?

You can use a macro

Sub RemApostrophe()
Dim Rng As Range
Dim myCell As Range
Set Rng = Selection
For Each myCell In Rng.Cells
myCell.Value = myCell.Value
Next myCell
End Sub


To install a macros

http://www.mvps.org/dmcritchie/excel/install.htm

Regards,

Peo Sjoblom





"Nino" wrote:

> I have a large Excel spreadsheet made up of about 30,000 cells.
>
> Most of them have a leading apostrophe (') in them, whether they are numbers
> or text.
>
> I would like to remove all the leading apostrophes without having to do it
> manually, cell by cell.
>
> Any ideas?

Does the apostrophe showing in the cell value? An alternative to a macro is
to use the functions RIGHT and LEN. For example, if the cell value of D5 as
'123, then in D6 put the function =RIGHT(D5,LEN(D5)-1) will return 123. The
same will work if cell value of D5 is 'abc

If you need to change 123 from a text to a numberic format, use the VALUE
function.



"Nino" wrote:

> I have a large Excel spreadsheet made up of about 30,000 cells.
>
> Most of them have a leading apostrophe (') in them, whether they are numbers
> or text.
>
> I would like to remove all the leading apostrophes without having to do it
> manually, cell by cell.
>
> Any ideas?

I had the same problem. They way I solve it was a bit different, but quick.
First I created three columns next to the columns containing the cells with
the apostrophes (with numbers or text).
In the first column I inserted a number followed by an apostrophe (i.e. 0' )
in all cells.
In the secont column I merged the containt of the previous cell with the
original cell by using =(B2&B1). This created a column where all cells
started with 0' followed by the original content. I then copied and pasted
only the value of the cell. With the comand "Text to Column..." I then split
this column using ' (apostrophe) as delimiter. This inserted in the 3rd
column the original value without the apostrophe.

"Nino" wrote:

> I have a large Excel spreadsheet made up of about 30,000 cells.
>
> Most of them have a leading apostrophe (') in them, whether they are numbers
> or text.
>
> I would like to remove all the leading apostrophes without having to do it
> manually, cell by cell.
>
> Any ideas?

=?Utf-8?B?ZmhhYmVybGFuZA==?= wrote

> I had the same problem. They way I solve it was a bit different, but
> quick.

Norman Jones offered this earlier this year:
Sub DeleteApostrophes()
Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Value
End If
Next rCell
End Sub

Also very quick

--
David

This works well, but is it possible to delete the apostrophes from all sheets
in a workbook?

"David" wrote:

> =?Utf-8?B?ZmhhYmVybGFuZA==?= wrote
>
> > I had the same problem. They way I solve it was a bit different, but
> > quick.
>
> Norman Jones offered this earlier this year:
> Sub DeleteApostrophes()
> Dim rCell As Range
> For Each rCell In ActiveSheet.UsedRange
> If rCell.PrefixCharacter = "'" Then
> rCell.Value = rCell.Value
> End If
> Next rCell
> End Sub
>
> Also very quick
>
> --
> David
>

Sub DoAllWorksheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
' delete apostrophes in ws using previous methods
Next
End Sub

Simon G wrote:
> This works well, but is it possible to delete the apostrophes from all sheets
> in a workbook?
>
> "David" wrote:
>
> > =?Utf-8?B?ZmhhYmVybGFuZA==?= wrote
> >
> > > I had the same problem. They way I solve it was a bit different, but
> > > quick.
> >
> > Norman Jones offered this earlier this year:
> > Sub DeleteApostrophes()
> > Dim rCell As Range
> > For Each rCell In ActiveSheet.UsedRange
> > If rCell.PrefixCharacter = "'" Then
> > rCell.Value = rCell.Value
> > End If
> > Next rCell
> > End Sub
> >
> > Also very quick
> >
> > --
> > David
> >

Hi Andrew
I am no programmer, but have copied and mixed the following code which,
unfortunatly, does not work. I would be grateful if you could check that
this is what you meant?

Sub DoAllWorksheets()
Dim ws As Worksheet
Dim rCell As Range
For Each ws In ActiveWorkbook.Worksheets
For Each rCell In ActiveSheet.UsedRange
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Value
End If
Next rCell
Next
End Sub


"Andrew Taylor" wrote:

> Sub DoAllWorksheets()
> Dim ws As Worksheet
> For Each ws In ActiveWorkbook.Worksheets
> ' delete apostrophes in ws using previous methods
> Next
> End Sub
>
> Simon G wrote:
> > This works well, but is it possible to delete the apostrophes from all sheets
> > in a workbook?
> >
> > "David" wrote:
> >
> > > =?Utf-8?B?ZmhhYmVybGFuZA==?= wrote
> > >
> > > > I had the same problem. They way I solve it was a bit different, but
> > > > quick.
> > >
> > > Norman Jones offered this earlier this year:
> > > Sub DeleteApostrophes()
> > > Dim rCell As Range
> > > For Each rCell In ActiveSheet.UsedRange
> > > If rCell.PrefixCharacter = "'" Then
> > > rCell.Value = rCell.Value
> > > End If
> > > Next rCell
> > > End Sub
> > >
> > > Also very quick
> > >
> > > --
> > > David
> > >
>
>

Hi Simon,

Change ActiveSheet to ws:

'=============>>
Sub DoAllWorksheets()
Dim ws As Worksheet
Dim rCell As Range

For Each ws In ActiveWorkbook.Worksheets
For Each rCell In ws.UsedRange
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Value
End If
Next rCell
Next ws
End Sub
'<<=============


---
Regards,
Norman



"Simon G" <[email protected]> wrote in message
news:[email protected]...
> Hi Andrew
> I am no programmer, but have copied and mixed the following code which,
> unfortunatly, does not work. I would be grateful if you could check that
> this is what you meant?
>
> Sub DoAllWorksheets()
> Dim ws As Worksheet
> Dim rCell As Range
> For Each ws In ActiveWorkbook.Worksheets
> For Each rCell In ActiveSheet.UsedRange
> If rCell.PrefixCharacter = "'" Then
> rCell.Value = rCell.Value
> End If
> Next rCell
> Next
> End Sub
>
>
> "Andrew Taylor" wrote:
>
>> Sub DoAllWorksheets()
>> Dim ws As Worksheet
>> For Each ws In ActiveWorkbook.Worksheets
>> ' delete apostrophes in ws using previous methods
>> Next
>> End Sub
>>
>> Simon G wrote:
>> > This works well, but is it possible to delete the apostrophes from all
>> > sheets
>> > in a workbook?
>> >
>> > "David" wrote:
>> >
>> > > =?Utf-8?B?ZmhhYmVybGFuZA==?= wrote
>> > >
>> > > > I had the same problem. They way I solve it was a bit different,
>> > > > but
>> > > > quick.
>> > >
>> > > Norman Jones offered this earlier this year:
>> > > Sub DeleteApostrophes()
>> > > Dim rCell As Range
>> > > For Each rCell In ActiveSheet.UsedRange
>> > > If rCell.PrefixCharacter = "'" Then
>> > > rCell.Value = rCell.Value
>> > > End If
>> > > Next rCell
>> > > End Sub
>> > >
>> > > Also very quick
>> > >
>> > > --
>> > > David
>> > >
>>
>>