Macro to check for duplicate words in a cell

[pharri11]

Greetings,
I am trying to figure out a macro to check for duplicate words in a single cell, and then reorganize it into a format which shows the # of words.

For example,

I have a cell that has 5 words in it:
Regulation; Support; Education; Support; Regulation

After performing the check for any duplicate words, I would (ideally) like the macro to then reorganize the cell, with the end result of the cell looking like this.

Regulation (2); Support (2); Education;

Any help is appreciated, thank you.

[jindon]

1) hit Alt + F11
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 to get back to Excel
Use in cell like
=CountWord(A1,";")

Please Login or Register  to view this content.

[pharri11]

I appreciate your help, thank you.

First thing,
I inserted the code in a new module, and did a test run with the text,
Regulation; Support; Education; Support; Regulation, in A1's cell contents.

However, upon entering =CountWord(A1,";") in cell A2, the result was this:

Regulation; Support; Education; Support; Regulation;Regulation;Support;Education

________________

Eventually, I plan to use this macro in another macro I'm already using, after inserting data into a cell, and then organizing using this. I'm wondering, did I do something wrong with the use of your code, or any way I can modify it?

Once again, thanks for your help.

[jindon]

OK then try this one

Please Login or Register  to view this content.

Note
code edited...

[pharri11]

It works, thanks!