I was given a dataset with a column containing the ways that a population paid for a specific service over the course of 4 encounters. In this column one cell contains ~4 ways a patient paid (e.g., Credit Card, Check, etc.) in a list format. I want to calculate the mode of this column (to get the method each person paid utilized most often) and to do this I want to convert the text to numeric codes and calculate the mode based on this.

I have found many ways to do this online for one piece of text in one cell, but how do I convert multiple lines of text into lots of numbers in Excel?

The data is presented like this:

I want to be able to convert the data into the following:

So I can ultimately get the following table:

Thanks in advance!

CodePudding user response：

For breaking the lists into individual elements, one can use formulae, but easier are:
a) Data | Text to Columns | Delimiter | choose comma
or one I found very recently:
b) Paste the data (without the header) into Notepad . Then copy it from Notepad and paste into Excel!
Beyond that, it sounds as though you know how to do the rest already.

CodePudding user response：

You don't say what the result should be if more than one Payment Form share the modal value, so I assume that is not possible.

For a Payment Form in B2:

=LET(ζ,TRIM(TEXTSPLIT(B2,",")),INDEX(ζ,MODE(MATCH(ζ,ζ,0))))