Excel track · Exercise 55 of 86 Paid

Text search summary

Combine FIND with MID for a slice, and a wildcard criterion for a filtered average.

Intermediate

Code, region and amount columns, sliced and matched

Read-only data · screen 1 of 2 · answer in D2

In D2, read the four digits that follow the hyphen in the code in A2.

	A	B	C	D
1	Code	Region	Amount	Answer
2	AN-1042	North	120
3	BO-2299	South	80
4	CY-3140	North	200
5	DE-4471	North	60

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In D2, round the average of the North amounts to one decimal place.

	A	B	C	D
1	Code	Region	Amount	Answer
2	AN-1042	North	120
3	BO-2299	South	80
4	CY-3140	North	200
5	DE-4471	North	60

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

Two techniques meet on this table. The first places a slice relative to a separator. FIND reports where the hyphen sits, one is added to step past it, and MID reads four characters from there. Nothing in that formula is a typed position that depends on this table.

The second is a pattern in a criterion. AVERAGEIFS with "N*" picks the three North rows, and the amounts come to 380 across them, so the average rounds to 126.7. The pattern answers a question about rows, not about positions.

Each half fails differently. A missing hyphen breaks the slice with #VALUE!, while a pattern that matches no rows leaves AVERAGEIFS with nothing to divide and returns #DIV/0!. Checking which error you got tells you which half went wrong.

Syntax

=MID(text,FIND("-",text)+1,4) finds the separator then slices after it; criteria take patterns

Worked example

On Code, region and amount columns, sliced and matched, =MID(A2,FIND("-",A2)+1,4) returns 1042.

=MID(A2,FIND("-",A2)+1,4)

Common errors

Error codes for MID driven by FIND beside a wildcard criterion, on one table
Error	What it means and how to fix it
`#VALUE!`	FIND cannot see the hyphen in the code, so the MID call receives no start position.
`#DIV/0!`	No row matches the pattern, so AVERAGEIFS has no numbers to average.
`Wrong slice`	Dropping the plus one returns -104, which is four characters starting at the hyphen.

At work

Slicing a code around its separator and averaging the rows that fit one pattern.