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?

Another way:

Formula:

Please Login or Register  to view this content.