How do I remove leading apostrophes in Excel?

[Nino]

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?

 

[Peo Sjoblom]

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

 

[Anki]

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.

 

[fhaberland]

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.

 

[David]

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

 

[Simon G]

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

 

[Andrew Taylor]

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

 

[Simon G]

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

 

[Norman Jones]

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

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