LinkBack URL
About LinkBacks
Hi,
I need to identify if a column text has a repeating pattern, how can I go about creating a formula?
The sample text is, Abcabc!abc123 dur to abc repeating this string should be marked.
Does anybody know a formula for this problem?
Regards
Jaffar
Find that the text has repeating pattern
Hi,
I need to identify if a column text has a repeating pattern, how can I go about creating a formula?
The sample text is, Abcabc!abc123 dur to abc repeating this string should be marked.
Does anybody know a formula for this problem?
Regards
Jaffar
Formula:
Please Login or Register to view this content.
Drill down
Frob first, tweak later
Unless you mean any string repeat, in which case you must specify the limit.
AAbAAn (is this a repeat of AA, or does it have to be 3 characters?)
It can be two, three or even 4 characters but not more than four.
However, we do not know it can be Abc or Cbc1cbc also. So how formula does not know which pattern is repeating to start with.
Does it have to be a single formula? If I break it up into simple steps, I see:
1) MID() function to extract all 3 character (or other length character) blocks of text from the main text string.
2) COUNTIF() function to see if there is more than one of any of the blocks of text
If any of the results of 2 is greater than 1, then you have a repeated block of text. If all of the results of 2 are 1, then there is no repeated block.
Originally Posted by shg
Thanks MrShorty and Neil.
I guess I need to combine the COUNTIF() and MID() functions together to get the final combined function, however, if I have to address all cases then the formula will be very long.
Try this ...
=IFERROR(LOOKUP(2,1/(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),MID(UPPER(A1),ROW(INDIRECT("$1:"&(LEN(A1)-2))),3),""))>3),MID(A1,ROW(INDIRECT("$1:"&(LEN(A1)-2))),3)),"")
Thanks Phuocam that works for me!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks