+ Reply to Thread
Results 1 to 9 of 9

If a cell begins with a 5 display that number if not display nothing

  1. #1
    Registered User
    Join Date
    04-01-2016
    Location
    nj
    MS-Off Ver
    10
    Posts
    3

    If a cell begins with a 5 display that number if not display nothing

    I have been trying to figure out a formula, but I can't seem to get one to work properly.
    If said cell begins with a 5 display that number, if it does not display nothing (blank)
    I am setting up a multi-worksheet spreadsheet so the formula would be referencing a number from another worksheet.

    Ex: if cell X2 starts with a 5 show that number in the current cell. If it doesn't show blank.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: If a cell begins with a 5 display that number if not display nothing

    Something like this...
    Please Login or Register  to view this content.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Registered User
    Join Date
    04-01-2016
    Location
    nj
    MS-Off Ver
    10
    Posts
    3

    Re: If a cell begins with a 5 display that number if not display nothing

    Can I use the same formula if the number starts with a 6 or a 1 to be used in the same cell.

    Ex:=IF(LEFT(X2,1)="6" or "1",X2,"")
    it doesn't seem to be working for me so now sure if it has to be changed around to be able to accommodate 2 numbers instead of 1

  4. #4
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: If a cell begins with a 5 display that number if not display nothing

    Please Login or Register  to view this content.
    should handle the 6 or the 1.

  5. #5
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: If a cell begins with a 5 display that number if not display nothing

    Try this..
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-01-2016
    Location
    nj
    MS-Off Ver
    10
    Posts
    3

    Re: If a cell begins with a 5 display that number if not display nothing

    Thank so much for your help....now I have a tricky situation.
    I am using the formula from above for the cell beginning with a 5 but...I need to use it in another cell where I need it to check to see if the cells begins with a 5 and if it does do a vlookup to find information pertaining to another cell. Below is what I have tried but it is giving me an error.

    =IF(LEFT('Concur Export'!X2,1)="5",(VLOOKUP('Concur Export'!$AC2,'Customer Job List'!$A$2:$B$112,2,FALSE),""))

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If a cell begins with a 5 display that number if not display nothing

    Quote Originally Posted by jjhayes View Post
    =if(or(left(X2,1)="6",left(X2,1)="1"),X2,""))
    Another way to write it:

    =IF(OR(LEFT(X2)={"6","1"}),X2,"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: If a cell begins with a 5 display that number if not display nothing

    Tony:

    Thanks! More compact. I will start using that for similar OR statements.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If a cell begins with a 5 display that number if not display nothing

    You're welcome. Thanks for the feedback!

+ 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. Replies: 3
    Last Post: 06-30-2015, 10:28 AM
  2. If number is Positive display in D20, if Number is negative display in D19
    By jlawrence123 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-13-2014, 09:07 AM
  3. [SOLVED] Display chosen number if cell text begins with specific letter
    By BPSJACK in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2014, 07:12 AM
  4. Find and display the last occurance in two columns and display related cell
    By willia97 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2012, 08:14 PM
  5. [SOLVED] Re: Conditional display - IF cell is blank, display next used row!
    By Patti in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2006, 05:35 PM
  6. [SOLVED] display total above certain number and display
    By MGD in forum Excel General
    Replies: 3
    Last Post: 03-04-2006, 01:03 PM
  7. Replies: 4
    Last Post: 03-08-2005, 08:06 PM

Tags for this Thread

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