Assigning a value based on case

[marknlard]

Hi,

I have a spreadsheet of a football fixture list with the game number in the columns and the team name on the row. The data in the middle is the team's opponent that week. So it looks something like:

........1.....2......3
ARS...new.LIV....mun
AVL...ful....BLA...WOL

and so on.

Names in lower case are away matches and upper case are at home. Is there a way of assigning a value to each cell depending on whether it is lower or upper case? So I can have a value of 1 for a home match and 2 for an away match, for example.

 

[p45cal]

Take you choice, X31 or Y31 and copy down:

Excel Workbook
	W	X	Y
31	wol	1	1
32	WOL	2	2
33	Wol	2	2
34	ABC	2	2
35	abc	1	1
Sheet

They only look at the first letter.
OK. I've done it the wrong way, swap the positions of the 2 and 1 in the formulae.

 

[marknlard]

Thanks p45cal, I used the first formula and it did the job. Great response!