How to remove the single quotation mark quickly?

[afatac]

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

 

[xenou]

Will it work if you use the CLEAN() Function? Just pull it down in adjacent columns to get a new set of data?

If it's okay to get output of all strings (which it should be if you want to export to text), then:

Sub Foo()
Dim c As Range, sTemp As String
    For Each c In ActiveSheet.UsedRange
        sTemp = c.Text
        c.Clear
        c.NumberFormat = "@"
        c.Value = sTemp
    Next c
End Sub

This is one of those funny things, I'm afraid. I'll have to search my notes. I've coerced values to text as I could only get rid of the marks with Clear, not ClearContents. So a date such as '1/1/2010 could become 40179 if we didn't use text.

 

[afatac]

One quick question: Is it possible to just export selected columns to tab delimited text file?

I do not seem to find such option. I always have to save the file to tab delimited text file.

 

[afatac]

I found that after saving the file to tab delimited text file, the single quotation mark is not included in the text file. Thanks goodness!

I will take note of other Excel files and see whether this is case for all of them.

Thanks for all the responses.

 

[xenou]

I found that after saving the file to tab delimited text file, the single quotation mark is not included in the text file. Thanks goodness!

I will take note of other Excel files and see whether this is case for all of them.

Thanks for all the responses.

Good. On the whole, Excel ignores them. It almost seems to be saved as a hidden formatting attribute (which I take from clear contents not removing the mark).

 

[Como67]

Hello all I am new to forum so forgive ignorance

I have the following problem I receive an data extract which I have no control over extract it holds multiple data like so - all data is carriage return in this cell



<TABLE style="WIDTH: 94pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=125 border=0 x:str><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 165.75pt" height=221><TD class=xl24 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 165.75pt; BACKGROUND-COLOR: white" width=125 height=221 x:str="'Quality Score: 39.37%
Frequency:
Halton: 22 (13.2%)
Knowsley: 18 (10.8%)
St Helens: 43 (25.7%)
Warrington: 35 (21.0%)
Wigan & Leigh: 49 (29.3%)
Respondents: 167">Quality Score: 39.37%
Frequency:
Halton: 22 (13.2%)
Knowsley: 18 (10.8%)
St Helens: 43 (25.7%)
Warrington: 35 (21.0%)
Wigan & Leigh: 49 (29.3%)
Respondents: 167</TD></TR></TBODY></TABLE>


I want to seperate these data out and want it to look like this

<TABLE style="WIDTH: 282pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=375 border=0 x:str><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" span=3 width=125><TBODY><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: white" width=125 height=35> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=125>Number</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=125>%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="'Quality Score: ">Quality Score: </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.39369999999999999">39.37%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Frequency:</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="Halton: ">Halton: </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>22</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.13200000000000001">13.20%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="Knowsley: ">Knowsley: </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>18</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.108">10.80%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>St Helens:</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>43</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.25700000000000001">25.70%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="Warrington: ">Warrington: </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>35</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.21">21.00%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="Wigan & Leigh: ">Wigan & Leigh: </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>49</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.29299999999999998">29.30%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="Respondents: ">Respondents: </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>167</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Any help would be much appreciated

Nico

 

[RoryA]

I suggest you post this as a new question rather than adding it to the end of a completely unrelated question.