How to change tally marks into numbers

[chynaruss]

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.

[#2]

@chynaruss 

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

[#3]

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

[#4]

@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?

[#5]

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

[#6 · Accepted 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.