need extract a common value from multiple cells

[udprocks]

Hi,

I need to extract data from two cells that is common value in both cells .

i have attached a sample sheet for same.

any help much appericiated.

 

[vletm]

@udprocks
Is there only one 'value' per cell or more?
(only like abc123 or also like abc123def456 ... a1b2c3d4 )

 

[Faseeh]

Hello,

This post should help
http://superuser.com/a/437398

...and you may look for the addin described there....
https://www.microsoft.com/en-us/download/details.aspx?id=15011

 

[Naresh Yadav]

Hi,

IF you allow us to add 3 extra column to get the numerical value of the data
you can use the attached file formula to get your desired result
here I also believe that your string of all numerical value will be one after another

regards
Naresh

 

[udprocks]

Hi,

IF you allow us to add 3 extra column to get the numerical value of the data
you can use the attached file formula to get your desired result
here I also believe that your string of all numerical value will be one after another

regards
Naresh

thanks naresh, its cool working for me.

 

[udprocks]

@udprocks
Is there only one 'value' per cell or more?
(only like abc123 or also like abc123def456 ... a1b2c3d4 )

hi vletm,

no its also like abc123def456

 

[Naresh Yadav]

Pls confirm that all the text string is Lower case or not?

 

[udprocks]

Pls confirm that all the text string is Lower case or not?

hi naresh,

its all in lower case.

 

[Naresh Yadav]

Hi,

if all the text text string is lower case than this is what I have
pls find the attached file for your understanding

regards
Naresh

 

[vletm]

@udprocks
One possible too ...

 

[udprocks]

many thanks,

naresh u are awesome.

both are awesome.

 

[shrivallabha]

I misread the requirement, most probably.
Which result is accurate
abc1da1f ---> a1a1a1a1a1a = 11111 (digit is found so report it)
abc1da1f ---> a1a1a1a1a1a = 11 (matching digit, count)
abc1da1f ---> a1a1a1a1a1a = 1 (matching single max case)

I thought third as requirement. Here's a formula which will give you maximum match number which needs to be entered by CSE.
=MAX(IF(ISNUMBER(FIND(MID(B2,COLUMN($A$1:INDEX($1:$1,LEN(B2))),ROW($A$1:INDEX($A:$A,LEN(B2)))),A2))*ISNUMBER(--MID(B2,COLUMN($A$1:INDEX($1:$1,LEN(B2))),ROW($A$1:INDEX($A:$A,LEN(B2))))),--MID(B2,COLUMN($A$1:INDEX($1:$1,LEN(B2))),ROW($A$1:INDEX($A:$A,LEN(B2)))),0))

 

[udprocks]

I misread the requirement, most probably.
Which result is accurate

abc1da1f ---> a1a1a1a1a1a = 1 (matching single max case)

I thought third as requirement. Here's a formula which will give you maximum match number which needs to be entered by CSE.
=MAX(IF(ISNUMBER(FIND(MID(B2,COLUMN($A$1:INDEX($1:$1,LEN(B2))),ROW($A$1:INDEX($A:$A,LEN(B2)))),A2))*ISNUMBER(--MID(B2,COLUMN($A$1:INDEX($1:$1,LEN(B2))),ROW($A$1:INDEX($A:$A,LEN(B2))))),--MID(B2,COLUMN($A$1:INDEX($1:$1,LEN(B2))),ROW($A$1:INDEX($A:$A,LEN(B2)))),0))

dear sir,

exactly third one is requirement like
its work like charm....

thanks you so much ,...

 

[bines53]

Hello friends,

Just for fun,
=MCONCAT(ohRemoveInvalidColumns(VALUE(MID(A2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17},1))))

MCONCAT---Morefunc add in
ohRemoveInvalidColumns---https://sourceforge.net/projects/quantlib/files/QuantLibXL/1.7.0/
QuantLibXL-1.7.0-bin.zip

David