+ Reply to Thread
Results 1 to 5 of 5

Check to see if a cell starts with an alphanumeric symbol and return value if it is

Hybrid View

  1. #1
    Registered User
    Join Date
    06-04-2011
    Location
    Saint Louis, MO
    MS-Off Ver
    Excel 2010
    Posts
    5

    Check to see if a cell starts with an alphanumeric symbol and return value if it is

    Hello,

    I need some help with making one cell check another cell's first character to see if it is either A-Z (caps don't matter) or 0-9 and return the value of a single cell if it is. The code that I am current using is:

    =IF(OR(AND(CODE(LEFT(D$10,1))>=65,CODE(LEFT(D$10,1)<=90)),AND(CODE(LEFT(D$10,1))>=97,CODE(LEFT(D$10,1)<=122)),AND(CODE(LEFT(D$10,1))>=48,CODE(LEFT(D$10,1)<=57))),C12,"")
    As I'm sure most know, this is supposed to change the first digit of cell D10 to ASCII and then check to see if it falls within one of those ranges that corresponds to A-Z, a-z, and 0-9, return a blank if not and the value of C12 if true. The problem is, I am able to put in symbols such as ?,!, etc into D10, and it still returns the value of C12. When typing in just the CODE(LEFT) part of the formula in another cell to see what D10 returns, it gives me a value of 63 for a ?, which falls out of those ranges, so the formula should return a blank correct? Is there any suggestion on what I can do to fix this problem?

    Thank you in advance.
    Last edited by MildewMan; 10-29-2014 at 07:59 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,874

    Re: Check to see if a cell starts with an alphanumeric symbol and return value if it is

    Here's another approach that is a bit easier to follow (and uses fewer function calls):

    =IF(ISNUMBER(FIND(LEFT(D$10),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789")),C12,"")

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-04-2011
    Location
    Saint Louis, MO
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Check to see if a cell starts with an alphanumeric symbol and return value if it is

    Quote Originally Posted by Pete_UK View Post
    Here's another approach that is a bit easier to follow (and uses fewer function calls):

    =IF(ISNUMBER(FIND(LEFT(D$10),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789")),C12,"")

    Hope this helps.

    Pete
    This worked perfectly. Thank you!

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Check to see if a cell starts with an alphanumeric symbol and return value if it is

    Another alternative:
    Formula: copy to clipboard
    =IF(LOOKUP(CODE(LEFT(D$10,1)),{0,48,58,65,91,97,123},{0,1,0,1,0,1,0}),C12,"")
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Check to see if a cell starts with an alphanumeric symbol and return value if it is

    If you want to go down the CODE route:

    =IF(OR(ISNUMBER(0+LEFT(D$10)),ABS(77.5-CODE(UPPER(LEFT(D$10))))<13),C12,"")

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ 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. [SOLVED] return the word interference if the cell contains < symbol
    By score in forum Excel General
    Replies: 4
    Last Post: 12-16-2012, 12:58 PM
  2. [SOLVED] Return Text if a cell contains the $ symbol
    By DeeLaa77 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2012, 09:01 AM
  3. Check if cell starts with certain character
    By ericc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-10-2010, 04:47 PM
  4. check if a Cell value starts with a Letter
    By vijay2482 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-02-2009, 09:53 AM
  5. Replies: 1
    Last Post: 09-14-2006, 10:16 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