Formula for assigning numerical value to text [Closed]

Report
-
Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
-
Hello,

I have a very large excel sheet with names. Some of the rows of names duplicate, sometimes more than once. In each row next to the name is a column of notes section where I have physically written Duplicate and then where it duplicates. In some cases where it duplicates more than once that info is written with the "Duplicate text" and then with an "&" to include additional duplication info.

I am hopoing to assign a value to each role based on the info in the notes section. For example when "Duplicate" is present I would like to assign a -1. When "Duplicate" and "&" I would like to assign a -2. If there is no "duplicate or &" in the note section than the value should be 1. Can anyone help?

1 reply

Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
448
Hi Daisy,

Give the following formula a try:
=IF(ISERROR(SEARCH("Duplicate",A2)),1,IF(ISERROR(SEARCH("&",A2)),-1,-2))

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!