Extract second numeric value in a column

[jaime1182]

Hi guys! Hope you have all been well.

I am getting stumped with a formula problem. I need to retrieve the second numeric value in a column.

Apple
Banana
123
Cupcake
987

At the moment the data looks like this (there are sometimes blank cells in between rows). I need the return to be "987"

I am going about this the very inefficient way by =ISNUMBER(A1) and then working out if it's the second "TRUE".

There has to be a smarter way to do this? Can someone help? Thanks

 

[Rick Rothstein]

Seem like there should be a simpler formula than the following. Anyway, I cannot test this for you as I do not have your version of Excel, but this works for me (change the range as necessary) although I am guessing you will have to commit the formula using CTRL+SHIFT+ENTER and not just Enter by itself...

=INDEX(A1:A10,LARGE(IF(ISNUMBER(A1:A10),ROW(A1:A10),),2))

 

[bebo021999]

Seem like there should be a simpler formula than the following. Anyway, I cannot test this for you as I do not have your version of Excel, but this works for me (change the range as necessary) although I am guessing you will have to commit the formula using CTRL+SHIFT+ENTER and not just Enter by itself...

=INDEX(A1:A10,LARGE(IF(ISNUMBER(A1:A10),ROW(A1:A10),),2))

I believe you really want to use SMALL.
Another way:

=IFERROR(INDEX(A1:A10,AGGREGATE(15,6,ROW(A1:A10)/ISNUMBER(A1:A10),2)),"Not found")

 

[Peter_SSs]

I believe you really want to use SMALL.

It would need more than just that change.

@jaime1182
I don't know if it would be likely for you but in case at a later date new rows might be inserted at the top of the worksheet, you might want to consider this type of formula that would still return the second number in the column. I have assumed that the second number would occur within the first 1,000 rows but you can make that number bigger if you want.

=INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(A:A,1):INDEX(A:A,1000))/ISNUMBER(INDEX(A:A,1):INDEX(A:A,1000)),2))

 

[bebo021999]

=INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(A:A,1):INDEX(A:A,1000))/ISNUMBER(INDEX(A:A,1):INDEX(A:A,1000)),2))

I wonder about what the differences are between the two ways of expressing range A1:A1000?
mine

ROW(A1:A1000)

and, you must have a reason of using this:

ROW(INDEX(A:A,1):INDEX(A:A,1000))

 

[jaime1182]

@Peter_SSs and @Rick Rothstein
Both solutions worked! Pete's on work computer (which has 365) and Rick's on my own old outdated version of Excel.

Thank you both!!

 

[Peter_SSs]

you must have a reason of using this:

Yes, I mentioned in my post. It may not happen for the OP but it relates to if rows might be added later. Here are our two formulas showing the correct result.

23 11 01.xlsm
	A	B	C
1	Apple	987	987
2	Banana
3	123
4	Cupcake
5
6	987
7	Apple
8	33
9	Pear
10	44
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IFERROR(INDEX(A1:A10,AGGREGATE(15,6,ROW(A1:A10)/ISNUMBER(A1:A10),2)),"Not found")
C1	C1	=INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(A:A,1):INDEX(A:A,1000))/ISNUMBER(INDEX(A:A,1):INDEX(A:A,1000)),2))

.. but look what happens if a new row (& possibly new data) is added at the top.

23 11 01.xlsm
	A	B	C
1	Heading
2	Apple	Apple	987
3	Banana
4	123
5	Cupcake
6
7	987
8	Apple
9	33
10	Pear
11	44
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IFERROR(INDEX(A2:A11,AGGREGATE(15,6,ROW(A2:A11)/ISNUMBER(A2:A11),2)),"Not found")
C2	C2	=INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(A:A,1):INDEX(A:A,1000))/ISNUMBER(INDEX(A:A,1):INDEX(A:A,1000)),2))

Both solutions worked!.... Rick's on my own old outdated version of Excel.

Are you sure? It returns not the second number but the second last number for me.

23 11 01.xlsm
	A			D
1	Apple			33
2	Banana
3	123
4	Cupcake
5
6	987
7	Apple
8	33
9	Pear
10	44
Sheet1

Cell Formulas
Range	Formula
D1	D1	=INDEX(A1:A10,LARGE(IF(ISNUMBER(A1:A10),ROW(A1:A10),),2))
Press CTRL+SHIFT+ENTER to enter array formulas.

work computer (which has 365)

With 365, you could also use this.
BTW, my previous formula should also work on any Excel version from Excel 2000 onwards (though it may(?) also need Ctrl+Shift+Enter confirmation)

23 11 01.xlsm
	A				E
1	Apple				987
2	Banana
3	123
4	Cupcake
5
6	987
7	Apple
8	33
9	Pear
10	44
Sheet1

Cell Formulas
Range	Formula
E1	E1	=INDEX(FILTER(A1:A10,ISNUMBER(A1:A10)),2)

 

[jaime1182]

Hi Peter. That's strange. Let me poke about why I was able to get that number I'm expecting. Having a gap between the numbers don't impact it but I am getting that "correct" number showing up on my other sheet. Unless ... there's a duplicate somewhere that just happens ... Garh. I hate big tables :'(

 

[Peter_SSs]

Unless ... there's a duplicate somewhere that just happens

That would be a possibility.
Simple way would be to test on a few smaller samples to see if it gives you confidence that it is working on a large sample.