Excel

Put this in the A column and copy to through the rest of the rows.

=IF(A1 = "#true", 1, 0)

Select allOpen in new window

Keep in mind, you have provided LITTLE information on the sheet and haven't even referenced the cells properly, so if it doesn't work, it's possibly because you're providing incomplete information.

Maybe Lee meant this...

Assuming the first column is A (with zeros) and the second column is B (with Text strings), try this...

In A1

=IF(B1="#true",1,0)

Select allOpen in new window

and copy it down.
Then copy the whole column A and paste is back as Values to get rid of formulas.

You can also use AutoFilter.

Apply a filter to the table of data and select the filter options on column B to show only the #True entries.

Select the first visible cell in column A and then hold Shift & then press End & Down, this will select all of the cells in column A but only thise that are visible. Type the number 1 and then press Ctrl & Enter which will populate the 1 into all selected cells.

Disable the filter and you will see all rows again.

@Neeraj
see the attachment. not working. each cell in the 1st column should be replaced 0 or 1.
@rob
autofilter won't work. #true is with lot much text in a single cell. so I need to find the tag #true and then change 0 or 1 in the other cell.
output.png

In that case, try this...
Change the format of Column A to General first if it's formatted as Text.

=IF(ISNUMBER(SEARCH("#sarcasm",B1)),1,0)

Select allOpen in new window

=IF(ISERROR(SEARCH("#true",B1)),0,1)

Select allOpen in new window

Auto Filter can be used to find a string of text within the cell and not just the whole cell text.

Rather than choosing the entry from the list, use the text input to show only cells that contain a string.

@Neeraj
I had written your code in the 1st cell but it remain as such. Am I doing wrong way? I have uploaded the file.

@Rob
Thanks. Could you kindly tell me how to go about this?
output.txt

What's the point of uploading the Text file? Please upload an excel file instead.

As I said earlier, check the format of column A. Is it set to Text? If so, change it's format to General, delete the cell content and reenter the formula and then copy it down.

Can you upload just a sample, rather than the whole file? Even the text file is 24Mb so won't guess at how big the Excel file will be.

@Neeraj
This is actually a text file as it has 1 column. When I downloaded it online, it has an extension .csv. So, we can say this is a csv file with 1 column.

My intention is to add a new column and classify/label each row with either 0 or 1 for that column and the two columns should be tab separated.  I am inserting a new column and then trying your code but si not working out.

@Rob
 I am having only this text file whose extension was .csv with 1 column. I am uploading the sample file of 7MB.
output.txt

What text are you looking for in the column to be marked as a 1?

See attached with screen shots/instructions for each stage. I have assumed "Sarcasm" is the word you're looking for as that is what was mentioned earlier.

If you need the output back as a text file, with the output sheet active save the file as txt or csv and only that sheet will be saved.
output.xlsx

If you just want file filtering, you can use some Powershell script like this:
NOTE: The match pattern just looks for #true.  It ignores matches such as #truelove and #trueblood.  Removing the "\b" from the end will get all #true variants.

Get-Content c:\users\mark\downloads\output.txt | 
    ?{$_ -match "#true\b"} | 
    Out-File -FilePath c:\users\mark\downloads\Filteredoutput.txt}

Select allOpen in new window

or use Notepad++
or use the FindStr function on the command line
or use some grep-like utility
or use some third-party utility

I'm not sure Excel is the best tool for the job.  However, PowerQuery does give you much functionality in this area.

Hi all,
Thanks all for the help. I will be trying all the options and respond once I get back to home after 2 days. I won't be having access to laptop for few time. Thanks again.

@yogesh

Where do you stand with your testing?