Find non-numbers in column

[davegugg]

Hi

Lets say I have the following going down column A:

Milwaukee
1
2
3
4
New York
5
6
7
San Francisco
8
Atlanta
9
10
11

Is there a combination of functions I could use to extract the non-numbers to a separate column? For example, my results would be

Milwaukee
New York
San Francisco
Atlanta

I know how to do via VBA, is there a way using worksheet functions?

[JBeaucaire]

1) Put all values in column A. Let's assume the range is A1:A100 (this is important, cannot use entire column)

2) In D1, enter this formula:
=COUNTA(A:A)-COUNT(A:A)

3) In E1, enter this array formula:
=IF(ROW(A1)>$D$1, "", INDEX($A$1:$A$100, SMALL(IF(ISTEXT($A$1:$A$100), ROW($A$1:$A$100), ""), ROW(A1))))
...confirmed by pressing CTRL-SHIFT-ENTER..."Milwaukee" should appear in the first cell

4) Copy E1 down as far as needed until values stop appearing.

[davegugg]

Thanks JB, this gave my unexpected results and I'm not sure why. When I apply your formula to my real-life need, it becomes:

{=IF(ROW('[RateSheetDocNumbers.xls]Doc Number Index'!A4)>$E$2,"",INDEX('[RateSheetDocNumbers.xls]Doc Number Index'!$A$4:$A$6017,SMALL(IF(ISTEXT('[RateSheetDocNumbers.xls]Doc Number Index'!$A$4:$A$6017),ROW('[RateSheetDocNumbers.xls]Doc Number Index'!$A$4:$A$6017),""),ROW('[RateSheetDocNumbers.xls]Doc Number Index'!A4))))}

My data goes from A4 to A6017 in a separate workbook called RateSheetDocNumbers.xls, and the worksheet is called Doc Number Index.
I put the counta - count formula on E2 of the worksheet I am looking to get the list to. When I entered the above, I got 3002 returned. When I copied down, I got 4002, 5002, 6002, blank. Cell E2 shows 7, which is the correct number of non-number values. I should also mention I have a custom cell number format in, of "0000".

I also have a couple of questions.
1) If I want to add to the "source" column later, can I make the formula go down to cell A10,000 in advance, or do I have to wait until there are values down to row 10,000?
2) Could I substitute the formula that is in cell E2 into the above formula, or does it have to be in its own cell?

Thanks a bunch!

[JBeaucaire]

This worked for me:

=IF(ROW(A1)>$E$1, "", INDEX([RateSheetDocNumbers.xls]Sheet1!$A$1:$A$10000, SMALL(IF(ISTEXT([RateSheetDocNumbers.xls]Sheet1!$A$1:$A$10000), ROW([RateSheetDocNumbers.xls]Sheet1!$A$1:$A$10000), ""), ROW(A1))))
...entered with CTRL-SHIFT-ENTER.


Warning....a range of 10000 cells may have a noticeable performance hit. Just be aware that the array is causing the lag.

[davegugg]

Strangely, I couldn't get it to work when I started in row 4, but when I switched it to row 1, it worked. This is solved as I can make that work, but if you have an explanation for that I'd be happy to hear it.

Thanks JB

[JBeaucaire]

My formula was designed for you to place it in any row for the first value, as long the formula still read ROW(A1) at the beginning and the end. The rest of the formula you could adjust, but not that starting value.