Extract 4 digit common numbers from 5 digit numbers

**ameque** · 06-22-2013, 02:39 AM

Hi,

trying to find a formula to extract a common 4 digit number from a list of 5 digit numbers

example:

12345
23456
22345
23457
41236
56789

2345 would be the common number for the above list. i know MODE can be used but only if the lists are all 4 digits.

thanks in advance.

**:) Sixthsense :)** · 06-22-2013, 03:00 AM

Try this...

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**JapanDave** · 06-22-2013, 03:37 AM

Try this, Adjust the range to suit your workbook.

Please Login or Register  to view this content.

**ameque** · 06-22-2013, 03:55 AM

Thanks

Sixthsense

this helped a lot.

how do i extract a 3 digit common (frequent) number from the same list using the same formula?

JapanDave,

not quite sure if this can help extract the frequent numbers i need from the list above. but thanks anyway.

**JapanDave** · 06-22-2013, 04:06 AM

Try the file

**ameque** · 06-22-2013, 04:36 AM

JapanDave,

yes i tried this but what i really want is the worksheet to find the most frequent numbers from the list using the MODE function like Sixthsense gave.

another problem im getting is that the result will get no value / error when number start from 0.

example

31872
69633
02164
02168
18464
54976

most common number should be 0216. how to rectify this? thanks.

**:) Sixthsense :)** · 06-24-2013, 01:09 AM

Try this...

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.