Solved

# Formula for excel

Posted on 2014-03-23
Medium Priority
116 Views
Hello! Help me please, to find a formula for excel, which takes all the words in the text (for example, text from column A) and gives all the words from the text without repeating in a column B.

For example,

Column A
Text

Although simplicity is a virtue, theories regarding pedagogy do not work in practice if they are black and white. To say that the best way to teach is only to praise positive actions and to ignore negative ones is like saying that strawberries reduce one’s risk for cancer so people should cut apples out of their diet and only eat strawberries. In both situations, there does not have to be a choice.

Column B - Words from text

although

simplicity

virtue

theories

regarding
…....
….....
0
Question by:victorya
• 2

LVL 2

Accepted Solution

smksa earned 1000 total points
ID: 39948677
Hi,

I found it might be done in two steps:

1. put your all text in notepad file, then in Excel use Data option from get data to txt file, use all default options for next, next until finish.

Now data comeup, every word in one column

2. copy all these columns and use paste special option, Transpose, now all data will be according to your demand. :)
0

Expert Comment

ID: 39948823
Is your original text in a single cell, or in multiple cells going down col A?

Do you want your result in a single cell, or in multiple cells going down col B? Are you asking for a single formula that can be copied to multiple cells in col B to provide the answer, or what?

This problem can be easily solved using a custom function, or a procedure ("macro") created with Visual Basic for Applications (vba). Are you familiar with vba? If I provide a vba procedure to solve your issue, is that acceptible?

What version of Excel are you using?
0

Author Comment

ID: 39980160
Honestly, I just need to figure out how to  correctly use formulas (like =VLOOKUP() or =MATCH() ) in order to compare array of words in column A with array of words in column B and a return difference between them.

For example

Column A      Column B       Column C

Although      positive              praise
actions          praise
positive         actions
motion           motion
0

Assisted Solution

RandomStu earned 1000 total points
ID: 40026296

Although
actions
positive
motion

In B1:B4 enter

positive
praise
actions
motion

In C1 enter

=ISERROR(MATCH(B1,A:A,0))

then copy down through C1:C4.

A "true" in col C indicates that the item in col B (of the same row) is NOT found anywhere in col A.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.