+ Reply to Thread
Results 1 to 6 of 6

formula recognising text and numbers

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    Eastbourne England
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: formula recognising text and numbers

    if you upload a book or sample it would be easier
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  3. #3
    Registered User
    Join Date
    05-23-2012
    Location
    Eastbourne England
    MS-Off Ver
    Excel 2003
    Posts
    5

    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
    Attached Files Attached Files

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    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.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Registered User
    Join Date
    05-23-2012
    Location
    Eastbourne England
    MS-Off Ver
    Excel 2003
    Posts
    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. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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