I have a spreadsheet in which columns can have values among a set of strings, for example Income column can have values such as: High, Low, Medium, etc. How to know which unique strings there are in values of a column and how to replace those strings with a number in Excel?

up vote 1 down vote accepted

To get the number of cells which have a specified string in it, use the following formulas:

=COUNTIF(B1:B4; "High")
=COUNTIF(B1:B4; "Medium")
=COUNTIF(B1:B4; "Low")

Adjust the range in the first argument to your needs.
The cell with one of these formulas will display the number of cells with the specified text.

To convert the text to a number, you can use either Search and Replace command (press Ctrl+H) or a temporary column with the formula:

=IF(B1="High";3;IF(B1="Medium";2;1))

Adjust B1 to your first cell and then drag auto-filler to copy the formula to other rows.


To find unique data, follow steps of Filter for unique values help article:

  1. Select the range of cells, or make sure the active cell is in a table.
  2. On the Data tab, in the Sort & Filter group, click Advanced.
  3. In the Advanced Filter dialog box, do one of the following:
    1. To filter the range of cells or table in place, click Filter the list, in-place.
    2. To copy the results of the filter to another location, do the following:
      1. Click Copy to another location.
      2. In the Copy to box, enter a cell reference.
  4. Select the Unique records only check box, and click OK.

Alternatively you can use the approach from the answer to Getting unique values in Excel by using formulas only, which is described in the following article: Create a unique distinct alphabetically sorted list, extracted from a column in excel

  • My main concern is how to find these string, there may be more so I do not want it to be done manually! – Akashdeep Saluja Dec 9 '13 at 7:08

You'll need to do this in VBA:

  • Declare a new dictionary
  • For..Next over the cells you consider
  • If the dictionary does not yet contain the value, add it along with a running number as key
  • For..Next over the cells again
  • Replace the values according the dictionary

I'm not having the time to write the code but I'm confident this points you towards a solution.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.