Non-disappearing apostrophe preceding text

[hall_damien@hotmail.com]

Dear all

I'm working with tab-separated data from .txt files, imported into Excel for statistical processing. I'm having the following problem with two of the columns in the tab-separated files I am importing.

The tab-separated .txt files are generated in the phonetic-analysis program Praat, and then I copy-paste the data from them into Excel. (All the data in the .txt files can be treated as text by Excel; what I do in Excel is count, sort etc - nothing very complicated.) Copy-pasting works fine but, for two of the columns, one of the things that could appear is a plus-sign. It is to be treated as text, but of course a bare plus sign with some letters after it makes Excel think that this is an incomplete formula. Therefore, when

+s

(for example) is pasted into an Excel cell, it generates the #NAME error.

I thought I could solve this by generating the .txt files so that they would have apostrophes before the relevant bits, so that what was pasted into a cell as

'+s

would just appear as

+s

(and be treated by Excel as if the apostrophe were not there) - but, when I generate the .txt files so that the raw .txt file has in it

'+s

and then paste that into Excel, the apostrophe doesn't disappear. Excel seems to be thinking of it as part of the value in the cell. Therefore, if I do this a number of times, I'll end up with a column which appears in Excel like this:

'+s
'^
'a
'uu
's

when what I want is this:

+s
^
a
uu
s

The problem, of course, is that when I do a COUNT function to operate over the column with these symbols in it, Excel does not count the ones it sees with an apostrophe, because it is including the apostrophe as part of the value of the cell. So the following:

'+s
'^
'a
'uu
's

COUNTIF(A1:A5,"+s")

yields the result zero, when it should yield 1 (because I do not want the apostrophe included as part of the value of the cell).

How do I get Excel not to return an error-message when it sees

+s

and yet not have the apostrophes actually displaying in Excel when I copy-paste the .txt files into it? In other words, how do I get Excel to recognise the contents of these cells as text, if the usual trick of putting an apostrophe before them doesn't work?

Many thanks for anyone's help!

Damien Hall

 

[cgmojoco]

Maybe try wrapping in quotes?/ turning the cells into formulae.

Private Sub WrapQuotes()
Dim LC As Long
Dim LR As Long
Dim myRNG As Range
Dim objCell As Object
Dim ws As Worksheet
ToggleEvents False
Set ws = ActiveSheet
LC = ws.UsedRange.SpecialCells(xlLastCell).Column + 1
LR = ws.UsedRange.SpecialCells(xlLastCell).Row + 1
Set myRNG = ws.Range(Cells(1, 1), Cells(LR, LC))
With ws
    For Each objCell In myRNG
        If objCell.Value <> "" And Not IsDate(objCell) Then
        objCell.NumberFormat = "General"
        objCell.Formula = "=" & """" & objCell & """"
        End If
        Next
End With
ToggleEvents True
End Sub
Sub ToggleEvents(blnState As Boolean)
'*********************************************
'*********************************************
'Toggles Display updates in Excel
'Written by Zack Barresse, aka firefytr
'*********************************************
'*********************************************
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState = True Then
            .StatusBar = False
        End If
    End With
End Sub

 

[Alan Maassen]

for two of the columns, one of the things that could appear is a plus-sign. It is to be treated as text, but of course a bare plus sign with some letters after it makes Excel think that this is an incomplete formula.

Damien Hall

good news for you if you upgrade to Excel 2003 - it treats +2 as text automatically, without a visible '. So if you have the chance it may be worthwhile in the long run.

 

[hall_damien@hotmail.com]

@cgmojoco:
Thanks! In order to use your code I will have to find out how to work with VBA, etc; I never have, though I am very keen to, so maybe this will spur me on!

@Alan Maassen:
I'm using Excel 2007 at the moment, though I use it in 97-2003 compatibility mode (ie disabling all features that came after Excel 2003), so that my files can be used by others who don't yet have Excel 2007. But, according to your post, even that should mean that my version of Excel would treat

+s

as text. It doesn't, though. Any idea why?

Thanks a lot to both!

Damien