# formula recognising text and numbers

1. ## formula recognising text and numbers

I have a spreadsheet witht he following formula - the intention is to list id numbers of individuals. Unfortunately when this was written the id's contained only numbers e.g 12345 since the inital development the id's of individuals have changed and there are now a mixture of text and numbers as well as number only. e.g. GOV6224

=IF(ISERROR(SMALL(\$R\$3:\$R\$34,ROW()-ROW(\$S\$3)+1)),"",SMALL(\$R\$3:\$R\$34,ROW()-ROW(\$S\$3)+1))

The formula only recognises the number only id's. I need the formula to recognise either and return an answer

Does anyone have any idea how I can achieve this?

Thanks  Register To Reply

2. ## Re: formula recognising text and numbers

if you upload a book or sample it would be easier  Register To Reply

3. ## Re: formula recognising text and numbers

Hi

I have added the worksheet only as the whole book was too large - hopefully this will be sufficient!

Thanks  Register To Reply

4. ## Re: formula recognising text and numbers

in D3 type
=IF(COUNTA(C\$3:C\$34)-COUNTIF(C\$3:C\$34,"")<ROWS(D\$2:D2),"",INDEX(C\$3:C\$34,MATCH(SMALL(IF(C\$3:C\$34="","",COUNTIF(C\$3:C\$34,"<"&C\$3:C\$34)),ROWS(D\$2:D2)),IF(C\$3:C\$34="","",COUNTIF(C\$3:C\$34,"<"&C\$3:C\$34)),0)))
and press Ctrl+Shift+Enter and fill down to D34. then copy those cells to columns I, N and so on.  Register To Reply

5. ## Re: formula recognising text and numbers

Hi

I have tried the formula suggested but it is returning the first answer for every row all the way down column D

Is there a \$ in the wrong place?

Thanks  Register To Reply

6. ## Re: formula recognising text and numbers

no-you array-enter it in one cell only and then copy and paste (or fill) that down. you can't enter it in multiple cells at once. see attached version of your sample workbook  Register To Reply