changing some random numbers to letters

mamboze

New member
Joined
Jun 4, 2013
Messages
2
Reaction score
0
Points
0
Hi guys
This is my first post.

I'm setting up multiple choice questionnaires and I need to assign the correct answer for each item randomly to the categories A, B, C, D. I've randomly selected numbers in the range 1-4 to each item, that's straightforward. The problem for me is how to assign to each of the random numbers a letter category so I can convert the numbers to letters and use this to locate the correct answer for each item.

Any help would be much appreciated. BTW, I hope I'm posting in the right forum :)
 
Hi Mamboze. You can use the CHAR function for this. To demonstrate, go to cell A1 and type in =CHAR(ROW(A1)) and push Enter. The ROW() part just returns the current row, so in cell A1 this will return 1. The CHAR part takes that ROW number and returns the particular character from your computer's character set that is associated with this number. On my PC, CHAR(1) returns some sort of non-printing character that looks like the cell is still empty. But as you'll see if you copy the formula down from row A1, there is a different character associated with the numbers 1 through 255. After 255, the formula just returns an error, because the CHAR function only handles 255 seperate numbers.

So if you copy that formula down to row 256, you'll see that things from your qwerty keyboard start appearing at row 33, numbers start at row 48, and letters start at row 65.

So:
=CHAR(65) gives us an A
=CHAR(66) gives us B
=CHAR(67) gives us C
=CHAR(68) gives us D

Now that we know the numbers associated with A through to D, all we need to do is start at CHAR(64) and add either 1,2,3,or 4 to the bit inside the bracket (i.e. the 64) to get Excel to return the letters A through D.
So if you've got random numbers between 1 and 4 in say rows B1:B10, then if you enter this into say cell A1 and drag it down, you can convert those random numbers to random letters:
=CHAR(64 + B1)

Or if you want to generate random letters from A to D from scratch without any helper cells, you can use this:
=CHAR(64 + RANDBETWEEN(1,4))
...which will return A through D randomly every time you push F9 (Refresh).

That should be enough to get you started.
 
Last edited:
thanks Jeffrey, that did exactly what I wanted.

best regards

mamboze
 
Back
Top