Get the Maximum from a Comma-separated String of Numbers

 

Problem description & analysis

Column A in an Excel file contains comma-separated strings of numbers, as shown below:

A

1

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

2

7,45,31,12

Our task is to get the maximum value and minimum value from each string and enter them into column B and column C respectively, as shown below:

A

B

C

1

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

15

1

2

7,45,31,12

45

7

The task involves string split, type conversion and getting the max/min value from an integer sequence.

Solution & explanation

Configure esProc add-in in Excel, restart the spreadsheet tool, and write the following code in cell B1:

=esproc("=?.split@cp().max()",A1)

Drag B1 down ore copy it down to calculate the maximum value for column A. split function splits a string into a sequence; @c option enables a comma-separated splitting, and @p option means automatic parse of data type, such as parsing an integer string as integers.

Similarly, we can replace max with min to calculate the minimum value.

Q & A Collection

https://stackoverflow.com/questions/63842304/how-can-i-get-the-minimum-value-from-a-comma-separated-cell-containing-number