Convert column of strings to number

[fox1589]

Hi!

I have a column of strings (animal names) and I want to convert the names into numbers (cow-> 1, zebra->3). The ordering of the numbers needs to be set by me- meaning, the names can't just be set to numbers in an arbitrary order, cow must be 1 and zebra must be 3 (sample file attached). Some of the strings contain hypens, I'm not sure if that affects anything, just letting you know.

Technically, I could do a Case Statement but in the full file I have over 30 names, so i didn't feel that writing 30 cases was so efficient...

Please let me know if you have any suggestions about the best way to do this.

Thanks so much!

[AliGW]

In C2, dragged down:

=VLOOKUP(A2,$F$2:$G$6,2,0)

Please give an example of strings with hyphens so that we can tweak to accommodate.

[fox1589]

Wow! I didn't know it was that simple. Thank you so much!! I tried it with the hyphen names and it works great!

[loginjmor]

Hi -

AliGW's formula should work, but your list in column F needs to be sorted alphabetically otherwise you may get errors or wrong numbers assigned to column C.

[AliGW]

Glad to have helped.

@ Loginjmor - as this is an exact match (0 or FALSE), the lookup table does not need to be arranged alphabetically. It is only with approximate matches (1 or TRUE) that the order of the lookup table matters.