SOLVED

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

@chynaruss 

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.

@chynaruss 

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)
Solution

@chynaruss 

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.