How to convert words into numbers in MS Excel 2010 spreadsheet

[K@ttY]

I have recently made a spreadsheet with the help of the Google docs and I have also transferred the required data in to spreadsheet into an excel file, I am currently running windows xp on my system and now I am willing to convert the words in to numbers in the spreadsheet that I have made just to explain the same in detail, here is a eg

In my spreadsheet there is 2 columns with male and female, so here I want to convert the word “male” in to 0 and the “female” to 1.

So is there anyone who can help me out with the same??

[Gania]

I will like to share that this will depend on the numbering system that you are having, I mean that if in your case if you are not having any relation between the number and the words then you can try to list the words in single column and you can use the adjacent column for the numbers, if there is any kind of relation between the words and the numbers like words >3 char = 1 then just let me know the same in detail. Juts check it out and then post the relation for further help.

[K@ttY]

Yes there is a relationship between the words and the numbers in the database that I am having, just to explain you it in detail:
I have made the spread sheet for the interview process, so here we ask some questions which has options like all of the above, none of the above and so, Here I am willing to convert the “all of the above” to 1 where as “none of the above” to 2 and then all the questions are summed up to give us the number of right answered question and all.
So is there a solution to do the same??

[Gania]

I think that you will have to define the words and their numbers on some other sheet because there might be a no of words that can have the same number, other than this there should not be same words having different value for eg if there is a question with answer yes or not then, yes for the 1 question will be 1 and then yes for the second question will be 2 and so, so this should not be the case and it will mess up everything, better follow the below possibility and this will be quite easy and simple as well:

• 1st question> respected answers
• 2nd question> respected answers

Just try it out and see if it is fine to your or not.

[Abhiroopa]

I am not very sure but then you can just try to follow the below countifs
=COUNTIF(A1:C10,"all of the above")
=COUNTIF(A1:C10,"none of the above")*2
Here I just wanted to say that it will depend on the layout that you are having.

[K@ttY]

Hey I am thankful to you both, just to give you all better idea about the thing that I am asking you I have attached the file and I have sent you through personal message, just go through it
Juts refer to the column A for example, there are options like facebook, orkut, gmail and so, here I want to convert the facebook in to 1, orkut in to 2, gmail in to 3, any further help regarding the same will be appreciated.

[Gania]

Hey I am thankful to you both, just to give you all better idea about the thing that I am asking you I have attached the file and I have sent you through personal message, just go through it
Juts refer to the column A for example, there are options like facebook, orkut, gmail and so, here I want to convert the facebook in to 1, orkut in to 2, gmail in to 3, any further help regarding the same will be appreciated.

Hey I have checked it out and I have also attached similar spreadsheet, you can also try to add some validation to the list ijn order to just limit the number of answers and reduce the errors as well, just check it out and see if you could get more help from the file that I have sent you in reply.

[Abhiroopa]

Even I have gone through it and I think that the formula that you are using is fine and there is a need to just replace few words that you want, I mean that you can use find/replace for the facebook and then replace it with 1 and then do the same for orkut and gmail and then replace them with 2 and 3, it won’t take much time, so just try it out and then let me know if you are able to do it correctly.

[K@ttY]

Hey thanks for the file that you have sent, it think that it is having the same case as of mine and referring the same has really helped me out, I am really thankful to you both for your help, I really appreciate it.