How to search for random patterns within a column

[Frustrated_excel274]

Hi All, I'm totally lost and have been searching for 2 days on how to do this. I have an excel spreadsheet of with each cell containing random numbers and letters. I want do a search that will compare the cells and automatically find any 5 character patterns. Case sensitivity doesn't matter. Example:
A1: FeexV6bAHb8ybZjq
A2: iVkebCz8JfEEXvgkPiM
A3: hg363dRjUgq2feExvXL

I would want a search function that would output "feexv" because it is the most common 5 character set found. Is there a way to do this?

 

[gurs]

The rule is quite clear, it says
In post#16 I was not saying it was acceptable to answer questions, simply by supplying a file. I was saying that you cannot upload files to this site, but would need to use a share site.

So are you saying that the TOS prohibit sharing a link to a file if that link isn't accompanied by an explanation of the solution, but if the link is preceded/followed by some explanation then it is not a TOS violation?

 

[gurs]

And it appears that my my latest post in which I have provided a link to my solution has again been deleted by one of the forum mods! Unbelievable. This despite the fact that I have provided full context for my solution and did not just post a link. This was not a solution that was "created elsewhere" on another forum or in response to another question. I spent time to create it specifically to address the questions asked by @Frustrated_excel274, and gave a high-level description of my solution. I did not "simply supply a file". And @Peter_SSs even posted a screenshot and full formula run-down from my proposed solution! Yet apparently that was not enough context to permit linking to the file. Even though linking to the file is permitted. Except when it's not.

At this point, I a simply baffled. Just trying to help the OP. If the mods think they are just applying the forum rules which are totally clear, I'm afraid they are mistaken. This is a circular discussion of Kafka-esque proportions.

 

[Fluff]

As long as you post the proposed solution to the thread (in full), then you can supply a link to a workbook as well. Although I don't really see the point of also supplying a workbook.

This despite the fact that I have provided full context for my solution and did not just post a link.

The only thing in the post I removed was a link to your workbook. There was no "full context for my solution" which is why it was removed.

The easiest why to supply a working solution is to use the XL2BB add-in XL2BB - Excel Range to BBCode

 

[gurs]

The point, @Fluff, is that some people are not Excel experts. And even for those who are, it takes about 3 seconds to download and open an XLSX file. If you try to follow the XL2BB output and recreate the file yourself, it will take much longer, even for a simple file like the one I posted.

Regarding what was taken down, one of the mods removed an entire post that Iput up in the last hour that contained a link to the file and many other words. Whatever. Doesn’t matter. The point was the file, so if the link is gone the post is worthless.

 

[gurs]

Dear @Frustrated_excel274,

I have created a solution to your problem that does not require the use of any VBA code. It is a simple XLSX file. It does use utilize array formulas, however, which need to be entered with CTRL-SHIFT-ENTER. My example file also uses conditional formatting on the output range to visually identify the larger numbers, although this is not necessary to solve your problem. In an effort to be helpful and save you (and future readers) time, I have posted my proposed solution to the cloud. It can be accessed here: Files shared via Tresorit. [NOTE TO FORUM MODS: Before deleting this post because it contains a filesharing link, please see below for additional explanation and context that should qualify this post for exclusion from Rule #4 despite the fact that it contains a link pursuant to the unwritten verbosity corollary.]

The sample strings you provided are in cells A2:A4. Cells B2:4 calculate the length of each such string, and cells C2:C4 calculate the number of sequential 5-character sub-strings (called “Blobs” in the file) that can be contained in each such string. Columns D:S contain every Blob for each string in the same row as the base string.

Each cell in range A7:A57 contains the array formula that looks to the Blob output range and isolates unique values, skipping blank cells. Range B7:B57 counts the number of times that the corresponding entry in column A appears in the Blob output range. Conditional formatting is then applied to B7:B57 so that the largest numbers are green, the middle range is yellow and the smallest numbers are red.

As I explained in earlier posts, this worksheet could be expanded to cover more strings by inserting rows above row 4 and filling down the formulas from B3:S3. You could also add rows to the output calculations by inserting rows above row 57 and filling down in columns A and B, but beware complications with the array formula. If you need to expand the output range, it may be easier to select cell A7, click into the formula bar, select/copy the full text of the formula, hit escape, then select the entire range from A7 to A(whatever row is now the last row), click in the formula bar again, paste the formula you just copied, and hit CTRL-SHIFT-ENTER.

There are no named ranges in this file. It was created in English on a Windows PC using Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20706) 64-bit. Functionality on older versions of Excel or other operating systems were not tested. This proposed solution comes with no guarantees or warranties, either expressed or implied. Nor, apparently, does my ability to post proposed solutions to this forum, so perhaps this post will be instantly deleted by the mods and never be seen or benefit anyone. But that is now in the hands of the mrexcel forum gods. I am but a simple forum member.