Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,942
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
can a symbol be inserted in front of existing data imsadrian Excel Discussion (Misc queries) 3 November 7th 07 04:44 PM
Automatic Removal of 0's in front of data James Excel Discussion (Misc queries) 1 May 28th 07 05:04 PM
How do I bring a certain data series to the front Adam P. Charts and Charting in Excel 1 January 3rd 06 09:36 PM
Front-End for an Excel Data Base Venkat Excel Discussion (Misc queries) 2 December 10th 05 06:50 AM
"Bring to Front" Data Labels? Rob Charts and Charting in Excel 2 April 3rd 05 04:59 PM


All times are GMT +1. The time now is 11:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"