Mode not recognizing Numbers and Text in same cell

[lucas257]

I have a mode formula that identifies common manufacturing part #'s but I cannot seem to get it to recognize text and numbers in the same cell...these are a series of part #'s so there is a combination of hyphens, numbers, and letters within the part # it's trying to find.

Here is the formula: =MODE(IF(ISNA(MATCH($H$3:$H$3000,$H$3001:H3001,0)),$H$3:$H$3000,""))


My luck this is something incredibly simple, must just be having a brain jam.

-Luke

[teylyn]

Hello,

please post a data sample to go with that formula. Post the expected result and explain the logic.

[FDibbins]

MODE works on values, but if you have any text in a cell with numbers, that cell becomes text, and is no longer seen as a value by excel. You will need to break the value out of the cell (or remove the text), first

[lucas257]

So if i have a part # that is VP5L2H18C581AA that is listed 15 times in the same column and would be my most repeating number in the column MODE couldn't detect it? I kind of understand the logic of not being able to find both text and numbers combined, but would have to think Excel has figured out a way get around this. You can set a mode for text, and for numbers separately; there is no special formatting or addition to the formula that would make it able to detect it?

[FlameRetired]

Two thoughts. Maybe not MODE but MAX(FREQUENCY? Or BASE(MODE(DECIMAL?

This must be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

	A	B
2	Michigan 26	Michigan 26
3	Wisconsin 30
4	Illinois 27
5	Michigan 26
6	Michigan 26
7	Wisconsin 30
8	Michigan 26

Since your profile shows Excel 2013, if there are no spaces or other non alpha-numeric characters (as in the lone example) or multiple blank cells.
This non array formula.

Please Login or Register  to view this content.

	A	B
2	MICHIGAN26	MICHIGAN26
3	WISCONSIN30
4	ILLINOIS27
5	MICHIGAN26
6	MICHIGAN26
7	WISCONSIN30
8	MICHIGAN26