+ Reply to Thread
Results 1 to 8 of 8

IF function and 'begins with'

  1. #1
    Registered User
    Join Date
    02-18-2014
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    57

    IF function and 'begins with'

    Hi,

    I have 15 possible values in 1448 rows of Column E: P01, P02, P03, P04, C01, C02, C03, X00, X01 and 1.0 2.0 3.0 4.0 5.0.

    I need a function that will name F02 cell "IFR" if cell E02 begins with letter P, "IFC" if begins with C, "As Built" if begins with X and left blank if otherwise.

    I tried function
    =IF(LEFT(A1)="P","IFI","IF(LEFT(A1)=”C”,”IFC",IF(LEFT(A1)=”X”,”As Built”,””) it doesn't show any mistake, only that it is too many arguments for this function.

    Is there any other than manual way to do that? In worst scenarion I could use filtering and three functions in three Columns and then copy-pasting them..

    Thanks,
    Lina

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

    Re: IF function and 'begins with'

    try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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...

  3. #3
    Forum Contributor
    Join Date
    12-16-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: IF function and 'begins with'

    I got your formula to work with a few changes

    =IF(LEFT(A1,1)="P","IFI",IF(LEFT(A1,1)=”C”,"IFC",IF(LEFT(A1,1)=”X”,”As Built”," K1")))

    http://www.easyexcelanswers.com

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: IF function and 'begins with'

    Try this regular formula:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: IF function and 'begins with'

    Be careful with LOOKUP...If non-listed values can be entered, they may match.
    Example:
    A1: NABC
    returns IFC because "N" is between "C" and "P"
    That may not be an issue here, though.

  6. #6
    Registered User
    Join Date
    02-18-2014
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: IF function and 'begins with'

    Quote Originally Posted by Ron Coderre View Post
    Try this regular formula:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Thank You Ron!! It worked well. What would I do without this forum...

  7. #7
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: IF function and 'begins with'

    Glad you got something you can use!

    Don't forget to mark this thread SOLVED (using the Thread Tools menu, above)

  8. #8
    Registered User
    Join Date
    02-18-2014
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: IF function and 'begins with'

    Good point thanks

+ 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. IF Function - Begins With
    By Cecil in forum Excel General
    Replies: 11
    Last Post: 06-04-2021, 12:52 PM
  2. [SOLVED] SUM column based on two dates, SUM function begins at first date which is a variable
    By moxiepilot in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-11-2013, 06:36 AM
  3. [SOLVED] if cell BEGINS WITH abc, then return 123, or if cell BEGINS WITH def, then 456, ...
    By Marijke in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-29-2013, 11:20 AM
  4. Begins With Function??
    By Excel_NooB in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-16-2007, 12:04 PM
  5. Begins with as criteria in function
    By jhorsley in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-31-2005, 02:05 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