SOLVED

How to change tally marks into numbers

Copper 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 (Copper 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.

1 best response

Accepted Solutions
best response confirmed by chynaruss (Copper 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.

View solution in original post