How to change tally marks into numbers

New Contributor

I currently have tally marks (uppercase I's) in an excel sheet and I am trying to convert it into values/numbers. Is there a quick way (with highlight) to change all the tally marks into numbers? Thank you.

5 Replies


Do you have one tally mark per cell, or multiple tally marks in one cell, or a combination?

Most are multiple (up to 5) marks per cell.


Let's say you have tally marks in cell C2.

The formula =LEN(C2) returns the number of tally marks.

This can be filled down if required.

Does that help?

It does work, however I am trying to replace the tally marks with the numbers. How do I write the formula out but still keep the tally marks (i hope this make sense).
best response confirmed by chynaruss (New Contributor)


If you want to replace the tally marks with numbers, you have two options

1. Using formulas

  • Use LEN formulas to return the numbers in another range.
  • Select the range with the formulas.
  • Copy it.
  • Without changing the selection, click the lower half of the Paste button and select Values.
  • You can now delete the range with tally marks.

2. Using VBA.

It would be possible to write a macro that replaces the tally marks with their count in the same cells.


If you want to keep the tally marks, just use the LEN formulas in another range.