# 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

2. ## Re: formula recognising text and numbers

if you upload a book or sample it would be easier

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

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.

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1