Find duplicate characters in a cell?

**Paik4Life** · 02-05-2011, 04:20 PM

Hey everyone,

I'm really hoping that this isn't a stupid question or worse, one that's been answered and that I just couldn't find using the search function.

The background to my problem is that I'm trying to create a unique dictionary for a game I'm programming. I have imported the dictionary into Excel and need to manipulate it with the following conditions:

Word length can only be 3-5 characters long
Word cannot contain special characters like hyphen or apostrophe
Word cannot have repeated characters

I got the first two criteria done using pretty brute for methods. The first using the LEN function to find words greater than 2 and those less than 6 and then comparing those two lists to get the list of 3-5 character words. The second I did manual search for characters and replaced them with six random characters so they would fail criteria 1 and be filtered out.

The last one I'm having problems figuring out. The game I'm programming only lets you use a letter once. So words like "dad" (or coincidentally enough "mom") would not be valid.

Is there a way I can look at the words in the cell and see if any character is repeated and then act upon that (e.g., copy to another column if no letters are repeated)?

Thanks in advance!

~Paik

**Ron Coderre** · 02-05-2011, 05:58 PM

With
A1 containing a word...
This formula will return the count of the most ocurring character:

Please Login or Register  to view this content.

Examples:

Please Login or Register  to view this content.

Is that something you can work with?

**Paik4Life** · 02-05-2011, 09:21 PM

That's fantastic. That's will work perfectly. I can use the resultant value to get what I need. I just have to make sure all the words are the same case since the value for "A" is different than "a".

Since I need the count to be 1, I can just do another column to make sure the value in that column is 1.

Thanks a lot, Ron!

~Paik

**Ron Coderre** · 02-06-2011, 12:39 AM

If you want the test to be case-INsensitive...try this variation:

Please Login or Register  to view this content.

I hope that helps.

**Paik4Life** · 02-06-2011, 12:43 AM

Thanks for the additional help. I just changed the source file to all lowercase using my text editor so I didn't need to worry about it. I really appreciate the help, it saved me a lot of heartburn!

~Paik

**FourLegsDrivenCat** · 07-15-2013, 03:39 AM

2Ron Coderre
Amazing. It took me half an hour to understand how it works.

**Rajesh Singh** · 08-21-2013, 04:43 AM

How to remove this duplicates text from a cell

**arlu1201** · 08-21-2013, 05:51 AM

Rajesh,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**arlu1201** · 08-21-2013, 05:51 AM

Rajesh,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**whabbear** · 06-02-2022, 12:40 PM

Hi, Ron! I've been trying to use your formula to get the count of the most frequently occurring character in each one of an range of cells in a column, all containing text, and not having any luck when I start in the beginning row and try to populate the formula across each succeeding cell. Would you have any suggestions for changing the formula to cover multiple items?

Thanks!

Rob McCann

**Glenn Kennedy** · 06-02-2022, 12:45 PM

Please don't post in an ancient thread. Start your own thread and (preferably) attach s sample sheet meeting forum guidleines (see yellow banner - top of page).