Request Excel formula formatting.(ChatGPT)

06/16/2024

Japanese version.

Here is how to have ChatGPT format Excel formulas with spaces and line breaks for easier reading.

This is especially useful for complex formulas.

Steps

Enter the following as an example.

Make Excel formulas easier to read.
=CONCAT(MAP(RANDARRAY(A2,1,1,COUNTA(A1:AK1),TRUE),LAMBDA(v,OFFSET(A1,0,v-1)))

Below is the full text of the response.


To make this Excel formula easier to read, we can break it down into smaller parts and write it as follows:

=CONCAT(
    MAP(
        RANDARRAY(A2, 1, 1, COUNTA(A1:AK1), TRUE),
        LAMBDA(v,
            OFFSET(A1, 0, v-1)
        )
    )
)

This formula randomly selects text from the range A1:AK1 and concatenates them into a single string. Here is what each function is doing:

  1. The COUNTA(A1:AK1) function counts the number of non-empty cells in the range A1:AK1.
  2. The RANDARRAY function generates an array of random numbers that has the same number of rows as A2 and the same number of columns as the count from step 1.
  3. The MAP function loops through each value in the array generated in step 2 and applies a function to it. In this case, the function is a LAMBDA function that takes the value as an input and uses the OFFSET function to return the text from the corresponding column in the range A1:AK1.
  4. The CONCAT function concatenates all of the text strings returned by the MAP function into a single string.

By breaking the formula down into smaller parts and indenting it, we can make it easier to read and understand. It is also helpful to understand what each function is doing in order to make sense of the overall formula.

---

The more complex it is, the more likely it is that you are breaking a formula (although, by and large, it is fine), so make sure it works.

Links

ChatGPT Articles

Tips. Others.(Microsoft Excel)