Home › Practice questions › IFERROR › Question 8

Excel IFERROR Practice Question: Each note may contain a reference like #101. Take the number after the # sign,…

By Rinkesh Shah, founder of Cellgrids.

The question

Each note may contain a reference like #101. Take the number after the # sign, or show No ref if there is none. Put it in B2 to B4.

The data

	A	B
1	Note	Ref
2	Order #101	?
3	No tag here	?
4	Item #7	?

The yellow cells are where your answer goes.

Show answer

Expected results:

B2 = 101
B3 = No ref
B4 = 7

How it works

Explanation: FIND gives an error when there is no # sign. IFERROR then shows No ref. MID takes up to 3 characters after the sign and VALUE changes them into a number.
Syntax: =IFERROR(VALUE(MID(A2,FIND("#",A2)+1,3)),"No ref")
Order #101 gives 101. No tag here has no #, so No ref. Item #7 gives 7.

Try it yourself in the live sheet

Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.

Try this question in the live sheet →

IFERROR syntax

=IFERROR(value, value_if_error)

Runs a formula, but shows a fallback value instead of an error if it fails. IFERROR is used to build decision rules directly into a spreadsheet instead of manually judging each row. Full IFERROR guide.