CODEWITHSUNDEEP
excelexcel-formula
JcoHut
JcoHut

Reputation: 33

How to fix Excel coding which return #VALUE

I have a column in Excel that return a value based on the string on the other column. For instance, Column A contains string: 

"(1) Missing Data For 1 Runs..(20%)"

and in the column B is: "20". The formula in the column B is:

 "=IF(ISBLANK(N14),"",(MID(RIGHT(N14,9),SEARCH("(",RIGHT(N14,9))+1,SEARCH(")",RIGHT(N14,9))-SEARCH("(",RIGHT(N14,9))-1)+0)*100)".

This formula works fine with the similar string with different percentage, such as 30%, 25%, etc, including there is no string or value in Column A, which return empty value in column B.

However, there is other string exist in column A, such as 

"(2) This spare part underwent repairs.."

in which the formula returns #VALUE in column B. Also, there is other string which combines both string, such as 

"(1) Missing Data For 1 Runs..(17%) 
(2) This spare part underwent repairs ..",

in which returns #VALUE as well in Column B.

What is the formula that returns the percentage number in the other column, in this case?

Upvotes: 0

Views: 81

Answers (1)

JLCH
JLCH

Reputation: 803

Ok, based on your feedback, if there is only on percentage per cell, use the "%" as anchor for your search (not the bracket as you did). Try following

=IFERROR(VALUE(MID(A13,SEARCH("%",A13)-2,2)),IFERROR(VALUE(MID(A13,SEARCH("%",A13)-1,1)),""))

how it works

VALUE(MID(A13,SEARCH("%",A13)-2,2))

will look for all instances where percentages have 2 digits and if so return 2 digits as a value, everything else throws an error and the second part applies. Second part

IFERROR(VALUE(MID(A13,SEARCH("%",A13)-1,1)),"")

will look for all intances with one digit percentages and returns this one digit as a value. Everything else throws an error which returns a blank.

Upvotes: 1

Related Questions