+ Reply to Thread
Results 1 to 4 of 4

Basing one cell off first 3 digits of another

  1. #1
    Registered User
    Join Date
    06-10-2016
    Location
    Austin
    MS-Off Ver
    2011
    Posts
    2

    Basing one cell off first 3 digits of another

    If I have one Cell that will always start with either 103, 201, or 103

    I want to another cell to read either Auto, II or NYC based on the beginning of that first cell

    103= II
    201= Auto
    301= NYC

    Is this possible?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Basing one cell off first 3 digits of another

    If the first cell is A1, put this in the second cell:

    =LOOKUP(NUMBERVALUE(LEFT(A1,3)),{103,201,301},{"II","Auto","NYC"})
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,178

    Re: Basing one cell off first 3 digits of another

    Try

    =IF(LEFT(A1,3)="103","II",IF(LEFT(A1,3)="201","Auto","NYC"))

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Basing one cell off first 3 digits of another

    If the cell value 'Always' begins with 103, 201 or 301 then you only need to test the first digit.

    =CHOOSE(--LEFT(A1),"II","Auto","NYC")

+ 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. Conditional Formatting cell color basing on matched value
    By chasoe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-27-2015, 06:09 AM
  2. restrict data entry basing on adjacent cell value
    By tkraju in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2014, 01:45 PM
  3. conditional formatting basing on cell above
    By Bishonen in forum Excel General
    Replies: 1
    Last Post: 12-12-2012, 08:13 AM
  4. [SOLVED] Hyde a row basing on cell contents (excel 2003)
    By lucaglr in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-30-2012, 10:07 AM
  5. Auto Formula basing on cell changes
    By jensenleesen in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-23-2009, 02:51 AM
  6. how to remove first x digits and last y digits from a cell?
    By maixent in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-28-2009, 02:06 AM
  7. Replies: 5
    Last Post: 12-08-2008, 11:47 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