Counting consecutive numbers in a comma-separated list

%3CLINGO-SUB%20id%3D%22lingo-sub-1970614%22%20slang%3D%22en-US%22%3ECounting%20consecutive%20numbers%20in%20a%20comma-separated%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1970614%22%20slang%3D%22en-US%22%3E%3CP%3EI%20tried%20to%20find%20a%20formula%20or%20a%20macro%20for%20this%20online%20but%20I%20couldn't%20find%20anything.%3C%2FP%3E%3CP%3EI%20need%20a%20way%20to%20calculate%20the%20consecutive%20numbers%20in%20a%20comma%20separated%20list.%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file%20for%20sample%20data%20and%20expected%20results.%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3EI'm%20using%20Excel%202016.%3C%2FP%3E%3CP%3E*%20In%20the%20sample%20file%2C%20I%20put%20the%20expected%20results%20in%20separate%20cells%2C%20but%20it%20can%20be%20in%20a%20single%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1970614%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

I tried to find a formula or a macro for this online but I couldn't find anything.

I need a way to calculate the consecutive numbers in a comma separated list.

Please see the attached file for sample data and expected results.

Thanks in advance.

I'm using Excel 2016.

* In the sample file, I put the expected results in separate cells, but it can be in a single too.

0 Replies