# How to Nest LEFT formulas

1. ## 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. ## 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?

3. ## Re: How to Nest LEFT formulas

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

4. ## Re: How to Nest LEFT formulas

This worked perfectly.... thank you!

5. ## Re: How to Nest LEFT formulas

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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