I'm working on my math stuff and i'm struggling with functions. So there are 4 'A', 4'B' and up to J. Out of 40 letters, computer randomly picks up two letters without replacement like A1 and B4 when the order is important. Then here is my real question. I need to convert those letters into assigned numbers such as A-0, B-1, C-2 and D-3. I tried to use IF(ISNUMBER("",0) function but i can't use more than 2 IF functions which means that i can only make function for A and B. How can i do it?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Function question.
0
Answers
0
Selected Answer
Hi Brian and welcome to the Forum.
You can use one form of the MATCH function for this.
If you have just a single a letter in cell A2, say, this formula will return the number you want:
=MATCH(A2,{"A","B","C","D","E","F","G","H","I","J"},0)-1The Match bit finds the position of what's in A2 within the array that follows (within curly brackets)- so A is 1, B is 2 and the -1 just takes one off (as you asked). The 0 in the formula means an exact match needs to be made.
If cell A2 contains a letter and a number like B4, the "letter's number" is returned by changing A2 in the formula to that in bold below:
=MATCH(LEFT(A2,1),{"A","B","C","D","E","F","G","H","I","J"},0)-1Note that the array could be replaced by a range of cells. There's more help in the Tutorials section (look for INDEX/MATCH).
Hope this works for you.
0
It's only partially clear what you want but here is another approach. Presuming that you have the letter "C" in A2, this is the basic formula that will convert it to the number 3:-
=FIND(A2,"ABCDEFGHIJ")Starting from this, if you want the same result when the entered letter in A2 is "a" (lower case), use the SEARCH function instead.
=SEARCH(A2,"ABCDEFGHIJ")Since you want A to be 0, B to be 1 etc, deduct 1 from the result of the formula.
=SEARCH(A2,"ABCDEFGHIJ"))-1This formula will return an error if the letter in A2 isn't in the list, say it's an "s". So, you make it return -1 in such a case.
=IFERROR(SEARCH(A2,"ABCDEFGHIJ"),0)-1Now we come to the part that isn't clear. If you want to create a combination like C-3 from then letter in A2 you would use a formula like this:-
=A2 & IFERROR(SEARCH(A2,"ABCDEFGHIJ"),0)-1And, of course, if the first letter and the second are different from each other, the formula below would combine B4 as the first letter with a number extracted from A2.
=B4 & IFERROR(SEARCH(A2,"ABCDEFGHIJ"),0)-1