Text string length to return set value.

[Ian Betteridge]

Good Morning All,
I'm after the following....
If the text string in column A is
less than 5 return 1
less than 9 return 2
less than 13 return 3
less than 17 return 4
& equal to or greater than 17 return 5
Example below.

Book1
	A	B	C	D
1	Machine Tag	Level
2	1234-LAY-PKG-W01		4
3	1234-LAY-PKG-W02		4
4	1234-MIN		2
5	1234-LAY-PKG-W01		4
6	1234-MIN-MAN		3
7	1234-LAY-PKG-W02-RFW222		5
8	1234-MIN-PKW		3
9	1234-MIN-PKW-W11		4
10	1234-MIN		2
11	1234-MIN-PKW-B14		4
12	1234-MIN		2
13	1234		1
14	1234-MIN-PKW		3
15	1234-MIN-MAN-M12-CVP121		5
Sheet1

Regards Ian

 

[jasonb75]

how about

=MATCH(LEN(A2),{0,5,9,13,17})

 

[Ian Betteridge]

Brilliant Jason, Thank You.

 

[jasonb75]

You're welcome Ian,

please check that you have the edited version of the formula, the original had the wrong values in the array.