Search code examples
excelexcel-formulaformulaexcel-2013

Trim up to n 0s

I have a calculated value (in column H) which gives the number of trailing 0s I'd like to trim from the right of a cell (column E). But I only want to remove 0s; if there are other characters they should remain.

Examples:

E |    H    | Output
--+---------+--------
3 | 0110A00 | 0110A
3 | 0110A0B | 0110A0B
1 | 0110A00 | 0110A0

Any ideas? I'd like to avoid macros or masses of nested IFs if possible.

Solution

  • Use:

    =LEFT(H1,MAX(AGGREGATE(14,6,ROW(INDEX(A:A,1):INDEX(A:A,LEN(H1)))/(MID(H1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(H1))),1)<>"0"),1),LEN(H1)-E1))

    enter image description here