Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete a ' from front of data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete a ' from front of data
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete a ' from front of data
"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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete a ' from front of data
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete a ' from front of data
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete a ' from front of data
hi
if the data is text, i doubt there is a way to remove the single quote. excel put it that as part of it's way of justifing the data. a single quote(') means the data is left justified. a caret(^) means the data is center justified. a double quote(") means the data is right justified. these mark do not show in the cell when typed alone. you have to type them with one of the other Justification mark in order to get them to show in the cell. to illistrate, in the format dialog, change the cell's justifcation from left to centered in the cell. you will see that the single quote change to a caret. no way to remove with text. it's an excel thing. regards FSt1 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can a symbol be inserted in front of existing data | Excel Discussion (Misc queries) | |||
Automatic Removal of 0's in front of data | Excel Discussion (Misc queries) | |||
How do I bring a certain data series to the front | Charts and Charting in Excel | |||
Front-End for an Excel Data Base | Excel Discussion (Misc queries) | |||
"Bring to Front" Data Labels? | Charts and Charting in Excel |