Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
?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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I remove leading apostrophes in Excel?
This works well, but is it possible to delete the apostrophes from all sheets
in a workbook? "David" wrote: ?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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I remove leading apostrophes in Excel?
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: ?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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I remove leading apostrophes in Excel?
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: ?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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I remove leading apostrophes in Excel?
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" wrote in message ... 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: ?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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove text leading zero in text string | Excel Worksheet Functions | |||
How do you remove a checkbox from an excel spreadsheet?? | New Users to Excel | |||
How do I remove decimals of IP address in excel? | Excel Discussion (Misc queries) | |||
How do I remove blank rows in Excel? | Excel Discussion (Misc queries) | |||
How to remove an Excel Main Menu item inserted by .xla file | Excel Discussion (Misc queries) |