• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting the character/Letter from from a word, repeated more than once

V

VDS

Member
@Dear All,

Is there any formula to take those letters from a particular word which is repeated more than once. For example, if i type a word is " ASSOCIATION", in A1, Column B1 should display those words A,S,O, I. Only repeated characters more than once in a set. just like taking sets in simple mathematics.

A1 = B1
ASSOCIATION = A,S,O, I
MIRROR = R
ENGAGEMENT = E, G, N, etc

IF all the characters of the word are different characters, the next column should display 0 or NIL Is this is possible ? Your help is much appreciated.


VDS
 
Hi VDS!

Try this.. CSE Formula..

=IFERROR(MID($A2,SMALL(IF(FREQUENCY(MATCH(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),0),MATCH(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),0))>1,ROW(INDIRECT("1:"&LEN($A2)))),COLUMN(A2)),1),"")
 

Attachments

In 2003+ version..

=IFERROR(Actual Formula),"Display Error Removal Data"

For 2003 Verison :
=IF(ISERROR(Actual Formula),"Display Error Removal Data",(Actual Formula))

Where in the above.. actual Formula is..

=MID($A2,SMALL(IF(FREQUENCY(MATCH(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),0),MATCH(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),0))>1,ROW(INDIRECT("1:"&LEN($A2)))),COLUMN(A2)),1)

Hope you can adapt it.. accordingly..
 
Hi Santhosh ,

Try this :

=MID($A2,SMALL(IF(FREQUENCY(CODE(MID($A2,ROW(OFFSET($A$1,,,LEN($A2))),1)),CODE(MID($A2,ROW(OFFSET($A$1,,,LEN($A2))),1)))>1,ROW(OFFSET($A$1,,,LEN($A2)))),COLUMN(A1)),1)

array entered using CTRL SHIFT ENTER. You will need to enter it in one cell , and then copy it across as many cells as you want ; the formula will return the repeated letters , and when none are found , return error values.

If you want to remove the error values , use ISERROR around the above formula , as in :

=IF(ISERROR(MID($A2,SMALL(IF(FREQUENCY(CODE(MID($A2,ROW(OFFSET($A$1,,,LEN($A2))),1)),CODE(MID($A2,ROW(OFFSET($A$1,,,LEN($A2))),1)))>1,ROW(OFFSET($A$1,,,LEN($A2)))),COLUMN(A1)),1)),"",MID($A2,SMALL(IF(FREQUENCY(CODE(MID($A2,ROW(OFFSET($A$1,,,LEN($A2))),1)),CODE(MID($A2,ROW(OFFSET($A$1,,,LEN($A2))),1)))>1,ROW(OFFSET($A$1,,,LEN($A2)))),COLUMN(A1)),1))

Narayan
 
@ Narayan Sir,

Thanks for your reply. Sir, one thing. When somendra is giving answer, it clicks so nice and perfect. May be tuning is good. Anyway, will work hard try till get a result.

VDS
 
Hi VDs,

This might not be an ideal solution but somehow it works. :) It uses ConCat function from Chandoo. see attached file.
 

Attachments

@Dear Fasheeh,

I tried to open in excel 2003 version. after opening, it displays # Name ? message in column B and Column AA. How to correct this?


VDS
 
You must log in or register to reply here.
Back
Top