How to delete a ' from front of data
in [General]
|
From: myasmana on 18 May 2010 11:48 I have a spreadsheet that needs to be converted here at work. The problem is that in a couple of columns such as 'South Dakota 'Illinois 'Nebraska You do not see the (') until you are in the cell, is there a global way to delete this? There are way to many to try and fix manually. Any help would be appreciated. Thanks, Barb
From: Mike H on 18 May 2010 12:05 Hi, Try this macro Sub Kill_Apostrophe() Dim c As Range Application.ScreenUpdating = False For Each c In ActiveSheet.UsedRange If c.HasFormula = False Then c.Value = c.Text End If Next c Application.ScreenUpdating = True End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "myasmana" wrote: > I have a spreadsheet that needs to be converted here at work. The problem is > that in a couple of columns such as 'South Dakota 'Illinois 'Nebraska > You do not see the (') until you are in the cell, is there a global way to > delete this? > There are way to many to try and fix manually. Any help would be appreciated. > > Thanks, > Barb >
From: myasmana on 18 May 2010 12:08 "myasmana" wrote: > I have a spreadsheet that needs to be converted here at work. The problem is > that in a couple of columns I have data with (') in front of each word such as : 'South Dakota 'Illinois 'Nebraska > You do not see the (') until you are in the cell, is there a global way to > delete this? > There are way to many to try and fix manually. Any help would be appreciated. > > Thanks, > Barb >
From: myasmana on 18 May 2010 12:19 Mike, Thank you so very much, this worked!! Saved us lots of time. Barb "Mike H" wrote: > Hi, > > Try this macro > > Sub Kill_Apostrophe() > Dim c As Range > Application.ScreenUpdating = False > For Each c In ActiveSheet.UsedRange > If c.HasFormula = False Then > c.Value = c.Text > End If > Next c > Application.ScreenUpdating = True > End Sub > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "myasmana" wrote: > > > I have a spreadsheet that needs to be converted here at work. The problem is > > that in a couple of columns such as 'South Dakota 'Illinois 'Nebraska > > You do not see the (') until you are in the cell, is there a global way to > > delete this? > > There are way to many to try and fix manually. Any help would be appreciated. > > > > Thanks, > > Barb > >
From: Mike H on 18 May 2010 12:23
Glad I could help and thanks for the feedback -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "myasmana" wrote: > Mike, > > Thank you so very much, this worked!! Saved us lots of time. > > Barb > > "Mike H" wrote: > > > Hi, > > > > Try this macro > > > > Sub Kill_Apostrophe() > > Dim c As Range > > Application.ScreenUpdating = False > > For Each c In ActiveSheet.UsedRange > > If c.HasFormula = False Then > > c.Value = c.Text > > End If > > Next c > > Application.ScreenUpdating = True > > End Sub > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "myasmana" wrote: > > > > > I have a spreadsheet that needs to be converted here at work. The problem is > > > that in a couple of columns such as 'South Dakota 'Illinois 'Nebraska > > > You do not see the (') until you are in the cell, is there a global way to > > > delete this? > > > There are way to many to try and fix manually. Any help would be appreciated. > > > > > > Thanks, > > > Barb > > > |