How to remove the single quotation mark quickly?

A

afatac

New Member
Joined
Dec 18, 2010
Messages
26
Hi,

I am going to save an Excel worksheet into tab delimited text file for importing into another application.

I notice that contents in some columns contain single quotation mark at the front of each string of text. I understand this is to 'force' the contents to be in text format.

How can I remove this single quotation mark? I do not want them to be in the tab delimited text file.

I am using Excel 2010
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
It is single quotation mark '

Yes, I have tried using Find and Replace, but it cannot be found.
 
Upvote 0
Hi,

Sorry, it's late over here - got exclamation & quote mixed up - me bad:rolleyes:

The following code will convert text to numbers if they are preceded by a single '

Highlight the column to change before running the macro.

Please test this on a copy of your data
Code: 
Sub ConvertText2Number()
 'NB highlight column before running macro
With Columns(Selection.Column)
.Copy
.TextToColumns Destination:=Range(Cells(1, Selection.Column).Address)
End With
End Sub

Cheers,
Ian
 
Upvote 0
Thanks for the help but...

The following code will convert text to numbers if they are preceded by a single '
Click to expand...

the content is not just numbers, it can also be e.g. 'Ref1234-12345-123
 
Upvote 0
What version of Excel are you using? What are some sample values that are marked this way (is it all values, or only some?)

I assume your data comes from another system (an import or export). Is that true?
 
Upvote 0
I am using Excel 2010.

Yes, the Excel file is given to me from someone. It is a 2003 Excel file.

It only happens to certain columns.

One example is 'ABC XYZ-REF-1201234
 
Upvote 0
Hi,

You may wish to try the following code, I don't know it's origins so I'm afraid I can't credit it to anyone specific.

Code: 
Option Explicit 
Sub DeathToApostrophe() 
    Dim s As Range, temp As String 
    If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _ 
    vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub 
    Application.ScreenUpdating = False 
    For Each s In ActiveSheet.UsedRange 
        If s.HasFormula = False Then 
             'Gets text and rewrites to same cell without   the apostrophe.
            s.Value = s.Text 
        End If 
    Next s 
    Application.ScreenUpdating = True 
End Sub

Please test on copy of your data:)

Ian
 
Upvote 0
Thanks.

I will try out.

By the way, one simple question. How do I restrict the Find and Replace function working on a particular column only?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
pivot table from columns with strings separated by commas
Replies
7
Views
318
Fluff
Fluff
HeRoseInThree
  • Question
I am trying to copy from Excel and paste elsewhere, and I am getting quotation marks (") before and after the selected text. Any way to fix this?
Replies
0
Views
475
HeRoseInThree
HeRoseInThree
U
  • Question
Remove Duplicates from a cell/ Create a cell with unique values from a cell with duplicate Values.
Replies
1
Views
392
eiloken
E
D
  • Question
Import and format txt file into open workbook.
Replies
4
Views
613
djdbg1
D
P
  • Question
Signature is being removed when I run the rest of my VBA
Replies
3
Views
162
rollis13
rollis13

Forum statistics

Threads
1,214,788
Messages
6,121,597
Members
449,038
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top