SOLVED

How to change tally marks into numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2293608%22%20slang%3D%22en-US%22%3EHow%20to%20change%20tally%20marks%20into%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293608%22%20slang%3D%22en-US%22%3E%3CP%3EI%20currently%20have%20tally%20marks%20(uppercase%20I's)%20in%20an%20excel%20sheet%20and%20I%20am%20trying%20to%20convert%20it%20into%20values%2Fnumbers.%20Is%20there%20a%20quick%20way%20(with%20highlight)%20to%20change%20all%20the%20tally%20marks%20into%20numbers%3F%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2293608%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293626%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20change%20tally%20marks%20into%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037106%22%20target%3D%22_blank%22%3E%40chynaruss%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20have%20one%20tally%20mark%20per%20cell%2C%20or%20multiple%20tally%20marks%20in%20one%20cell%2C%20or%20a%20combination%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293630%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20change%20tally%20marks%20into%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293630%22%20slang%3D%22en-US%22%3EMost%20are%20multiple%20(up%20to%205)%20marks%20per%20cell.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293636%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20change%20tally%20marks%20into%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037106%22%20target%3D%22_blank%22%3E%40chynaruss%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20tally%20marks%20in%20cell%20C2.%3C%2FP%3E%0A%3CP%3EThe%20formula%20%3DLEN(C2)%20returns%20the%20number%20of%20tally%20marks.%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20down%20if%20required.%3C%2FP%3E%0A%3CP%3EDoes%20that%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.