+ Reply to Thread
Results 1 to 5 of 5

length and alphanumeric formula (quick formula question)

  1. #1
    Registered User
    Join Date
    02-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    71

    Red face length and alphanumeric formula (quick formula question)

    I have 2 columns A and B
    Both have similar kind of information (numeric and alpha numeric data with character length going up 60)


    I want to filter the data in those 2 columns by max. character length 16 and only alphanumeric data. How do I do it?

    Thanks
    Last edited by nawGo; 06-17-2014 at 04:00 PM.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: length and alphanumeric formula (quick formula question)

    I'm not entirely sure how you want to filter (I'm assuming that both columns need to meet both criteria but I would suggest creating a helper column with something like:
    =AND(LEN(A1)<=16,ISNUMBER(SUM(IF(FIND(MID(A1,ROW(A$1:A$16),1),"0123456789abcdefghijklmnopqrstuvwxyz",1),1,0))),LEN(B1)<=16,ISNUMBER(SUM(IF(FIND(MID(B1,ROW(A$1:A$16),1),"0123456789abcdefghijklmnopqrstuvwxyz",1),1,0))))
    as an array formula (confirm with ctrl+shift+enter) and then filtering on that column.

  3. #3
    Registered User
    Join Date
    02-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: length and alphanumeric formula (quick formula question)

    Thanks ragulduy, great idea!

    I used your formula, I moved columns A/B to Q/R
    =AND(LEN(Q2)<=16,ISNUMBER(SUM(IF(FIND(MID(Q2,ROW(Q$2:Q$401),1),"0123456789abcdefghijklmnopqrstuvwxyz",1),1,0))),LEN(R2)<=16,ISNUMBER(SUM(IF(FIND(MID(R2,ROW(R$2:R$401),1),"0123456789abcdefghijklmnopqrstuvwxyz",1),1,0))))

    it's working for some of the fields and for some it's not.

    E.g.
    (it's picking false for the following even though it should say True)

    PASUPINVHPCM08



    What am I doing wrong?
    Last edited by nawGo; 06-17-2014 at 12:31 PM.

  4. #4
    Registered User
    Join Date
    02-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: length and alphanumeric formula (quick formula question)

    It's the mid function that's causing this discrepancy, what else can I use?

  5. #5
    Registered User
    Join Date
    02-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: length and alphanumeric formula (quick formula question)

    I was able to gifure it out...

    alpha numeric:

    =AND(SUMPRODUCT(--ISNUMBER(SEARCH(MID(Q2,ROW(INDIRECT("q$2"&LEN(Q2))),1),"abcdefghijklmnopqrstuvwxyz")))>0,SUMPRODUCT(--ISNUMBER(--MID(Q2,ROW($1:$999),1)))>0,SUMPRODUCT(--ISNUMBER(SEARCH(MID(R2,ROW(INDIRECT("R$2"&LEN(R2))),1),"abcdefghijklmnopqrstuvwxyz")))>0,SUMPRODUCT(--ISNUMBER(--MID(R2,ROW($1:$999),1)))>0)

    Length :

    =AND(LEN(Q2)<=16,LEN(R2)<=16)
    Last edited by nawGo; 06-17-2014 at 04:00 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. quick formula help - replicate alphanumeric string but increase by 1
    By JoeMac in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 07-01-2013, 04:56 PM
  2. [SOLVED] Quick formula question
    By slcphoto in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-04-2012, 07:09 PM
  3. Quick formula question
    By ScottO in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 12:05 PM
  4. [SOLVED] Quick formula question
    By jjjam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. Quick formula question
    By jjjam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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