Convert words into numbers

**Onderzoek** · 08-31-2009, 05:25 AM

Hi,

For my research in medicine I build a spreadsheet with google docs. After having received most of the data I transfered the spreadsheet into an excel file. The next thing I would like to do is to convert the words in the spreadsheet into numbers so I can calculate with them. For example: every row in column E consist of one of the two words: 'man' or 'woman'. I would like to convert the word 'man' into a '0' and the word 'woman' into a '1'. Is it possible to write a formula or function for this?

Thank you very much!

With kind regards,

Thomas

**Yard** · 08-31-2009, 05:29 AM

Hi,

It depends on the criteria for your numbering system.

If there is no relationship between the word and the number, then you could just list all the words in one column, their numbers in the adjacent column and lookup each one.

If there is some relationship, e.g. words with >3 characters = 1, then you need to explain the relationship.

HTH

**Onderzoek** · 08-31-2009, 05:48 AM

Hi,

Thank you for your reply. In my database there is a relationship between the numbers. Let me give yoy another example.

We askeds patients to answer the following question: Do you have pain in your hip? The possible answers are never, sometimes and always. I would like to convert never into 1, sometimes into 2 and always into 3. All the 'points' of the different questions need to be summed up and will give a final 'number' which tells us something about the condition of the patient.

Do you have any idea how to solve this problem?

Thanks!

**zbor** · 08-31-2009, 05:59 AM

On second sheet you must define all words and their numbers.

There can be several words that getting same number (never into 1, sometimes into 2 and always into 3) or (yes into 1, maybe into 2 and no into 3) but there can't be same words having different value (1st question Yes into 1,second question Yes into 2, third question Yes into 3).

Also, it would be nice if you could write all possibilities that you can have:

1st question -> those answers,
2nd question-> those answers etc.

Also, much can depend of how you have sorted your data.
Some example workbook would be nice.

**martindwilson** · 08-31-2009, 06:11 AM

you could just use countifs
=COUNTIF(A1:C10,"never")
=COUNTIF(A1:C10,"sometimes")*2
=COUNTIF(A1:C10,"always")*3
but it does depend as zbor says on your layout.

**Onderzoek** · 08-31-2009, 06:17 AM

Thank you Zbor and Yard for your fast replies!

I've attached a part of my excel file so you can see how I have sorted the data (the language is dutch).

Lets take column B as an example. I would like to transfer: geen into 1, gering into 2, matig into 3 etc. The 'geen' in column B gets te same number as the 'geen' in all the other columns.

If it is possible to convert the words into numbers, how do I do that? Do I have to write a formula?

Thanks again!