CODEWITHSUNDEEP
excelexcel-formulazero
Cameron Mah
Cameron Mah

Reputation: 1

Adding IFBLANK turn into 0 on an existing formula

I used this function =VALUE(RIGHT(D2,LEN(D2) - MAX(IF(ISNUMBER(MID(D2, ROW(INDIRECT("1:"&LEN(D2))), 1) *1)=FALSE,ROW(INDIRECT("1:"&LEN(D2))), 0)))) to remove only the right most numbers from a string mixed of letters, special characters, and numbers. Sometimes there are no numbers at the end of the string in which it returns a blank cell. I would like to change the blank cell, however, when I try to select blank cells using "Go to Special" in excel it says "no cells found"

I am wondering if there is a way for the formula to just input zero for blank cells instead

I tried using =VALUE(IF(ISBLANK(D2), 0, RIGHT(D2,LEN(D2) - MAX(IF(ISNUMBER(MID(D2, ROW(INDIRECT("1:"&LEN(D2))), 1) *1)=FALSE,ROW(INDIRECT("1:"&LEN(D2))), 0))))) and =VALUE(IF(D2=" ", 0, RIGHT(D2,LEN(D2) - MAX(IF(ISNUMBER(MID(D2, ROW(INDIRECT("1:"&LEN(D2))), 1) *1)=FALSE,ROW(INDIRECT("1:"&LEN(D2))), 0))))) however it just continues to return blank cells.

Upvotes: 0

Views: 104

Answers (1)

John White
John White

Reputation: 1

Check to see if you have this option checked in the Excel Options. It might work.

Show a Zero in cells that have zero value

Upvotes: 0

Related Questions