Find repeating Text

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Dec 22, 2009

• #2

Thiyagu said:

Anyone got idea to find repeating text and no ,,example 120KSV.
If for no i can use function mode() , but it's not work for text and no together.Example 120KSV.

Pls help

Click to expand...

If

120KSV

is an example/sample, what is the expected outcome?

 

Upvote 0

B

Bill Rockenbach

Well-known Member

Joined

May 4, 2009

Messages

1,425

• Dec 22, 2009

• #3

Please post sample of text to parse out repeating text. Is it a column of data, multiple columns, etc. ?

How do you want to identify repeating text, highlight, post to another column, etc?

You can use the filter to filter out repeat text.

Need more info.

 

Upvote 0

T

Thiyagu

Board Regular

Joined

Nov 21, 2009

Messages

131

• Dec 22, 2009

• #4

example

120ksv 310
120ksv 310
140ksv 305

If i use mode function for no the result is 310 , but for the text & no
the resullt is NA ..i Want to have system show 120ksv.

 

Upvote 0

T. Valko

Well-known Member

Joined

May 9, 2009

Messages

16,623

• Dec 22, 2009

• #5

If you want to find the most occuring TEXT entry try this array formula**:

=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Assumes no empty cells. Also note, it works just like the MODE function does for numbers. If there is more than one instance of a mode the formula will return the first instance. If there is no mode you'll get an error.

 

Upvote 0

B

Bill Rockenbach

Well-known Member

Joined

May 4, 2009

Messages

1,425

• Dec 22, 2009

• #6

Assume list starts in cell A2. Put this formula in cell B2 and Copy down

=IF(COUNTIF(A$2:A2,A2)>1,A2,"")

 

Upvote 0

T

Thiyagu

Board Regular

Joined

Nov 21, 2009

Messages

131

• Dec 22, 2009

• #7

Thanks for the help. Now this problem solve after follow the instruction from u all.

 

Upvote 0

B

Bill Rockenbach

Well-known Member

Joined

May 4, 2009

Messages

1,425

• Dec 22, 2009

• #8

What solution did yopu use?

 

Upvote 0

T

Thiyagu

Board Regular

Joined

Nov 21, 2009

Messages

131

• Dec 22, 2009

• #9

Both solution also work fine.
Thanks for u all for the great support

 

Upvote 0