count duplicate characters at the end of a dynamic string

[hopefulhart]

hello, i have a range of cells that get filled with the letters "W", "L", "O", and "T" from a series of macros. what i am wondering if there is a simple way to analyze the string, and depending on which letter is last, count how many of that letter are in a row at the end of the string.

examples: string "W" desired outcome "1" as there is only the one character
string "LOOTWW" desired outcome "2" as the last two characters are the same
string "WWWLWWWWWW" desired outcome "6" as the last six characters are the same

i have the following code that does the trick, however i was wondering if there was a simpler way to achieve this as it is quite long and bulky.

Please Login or Register  to view this content.

the whole code includes the same for the other characters

thanks to anyone who has any suggestions


*well i was just about to post this and then i thought maybe a for loop would work and quickly started tinkering and came up with something nice and simple that seems to work, but i will post anyways in case someone could find it useful

Please Login or Register  to view this content.

:D

[berlan]

Maybe:

Please Login or Register  to view this content.

and use for instance by:

Please Login or Register  to view this content.

or directly with reference to a cell with the same info, e.g. =CountW(A1)

Hope this helps.

Best,
berlan

[skywriter]

I was also thinking of using Regular Expressions, which I am new to, but I took a different approach than Berlan.

Please Login or Register  to view this content.

[kalak]

For your OP question, with string in cell C5 (this post is just for the record)

Please Login or Register  to view this content.