+ Reply to Thread
Results 1 to 5 of 5

How to Nest LEFT formulas

  1. #1
    Registered User
    Join Date
    11-20-2023
    Location
    Westfield, IN
    MS-Off Ver
    365
    Posts
    3

    How to Nest LEFT formulas

    Hello! Need help making BOTH of these formulas work together.

    Brief description of the problem. I am working with a column of numbers where I always need to remove the last number, and if the first number is zero, then I also need to remove that.

    Formula A:

    =LEFT(C4,LEN(C4)-1) Simply removes the last digit.

    Formula B:

    =IF(LEFT(C4,1)="0",MID(C4,2,225),C4) Removes the first digit if it is 0

    At first, I thought it is as simple as nesting any other IF, but since I need both formulas to always work, that is failing. Appreciate any help!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,630

    Re: How to Nest LEFT formulas

    Rather than nest LEFT() formulas, I think I would do it all inside of a MID() function. Starting with your MID() function MID(C4,2,225):

    1) I want the text extraction to start at the first position if the text begins with anything other than a "0" and at the second position if the first character is "0". An IF() with LEFT() can determine if the leftmost character is "0" LEFT(C4,1)="0"
    2) As noted, I want to start at character 1 most of the time, but start at character 2 when the leftmost character is "0" 1+IF(LEFT(C4,1)="0",1,0) will return 2 when C4 begins with a "0" and will return 1 when C4 does not.
    3) Similarly, I want to extract all but the last character of whatever is left. LEN(C4) gives me the length of C4. I subtract 2 from LEN() when the leftmost character is "0" or 1 when the leftmost character is something else LEN(C4)-1-IF(LEFT(C4,1)="0",1,0).
    4) Put those inside of the base MID() function =MID(C4,1+IF(...),LEN(C4)-1-IF(...))

    Would something like that work for you, or would you prefer to figure out a different but equivalent nested logic of IF()/MID()/LEFT() functions?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2309
    Posts
    5,142

    Re: How to Nest LEFT formulas

    Another way:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-20-2023
    Location
    Westfield, IN
    MS-Off Ver
    365
    Posts
    3

    Re: How to Nest LEFT formulas

    This worked perfectly.... thank you!

  5. #5
    Registered User
    Join Date
    11-20-2023
    Location
    Westfield, IN
    MS-Off Ver
    365
    Posts
    3

    Re: How to Nest LEFT formulas

    thank you so much.... I was trying this when I got the other response... Appreciate it.

+ 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] How to Nest 2 Formulas That Aren't Related
    By spacle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-08-2021, 02:29 PM
  2. [SOLVED] Trying to Nest Formulas LOOKUP and IF
    By JSelmstar in forum Excel General
    Replies: 5
    Last Post: 04-10-2018, 06:56 PM
  3. Countifs with nest left
    By namluke in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-22-2013, 06:33 AM
  4. [SOLVED] Nest LEFT in VLOOKUP producing error
    By msawyer in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-07-2012, 07:26 PM
  5. Excel 2007 : Can't Nest Formulas
    By mick2 in forum Excel General
    Replies: 1
    Last Post: 09-28-2010, 07:04 PM
  6. HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION
    By CHAIM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  7. [SOLVED] HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION
    By CHAIM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06: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