Skip to main content

I have a multiple select field called “Weaknesses” in which a client can select different areas they’re struggling in with the language they’re learning. 

I’d love a formula that takes their responses and transform it to 1 word weaknesses and puts them together in a grammatically correct sentence. 

So... If they say “I can't stick to goals”, then I want the return to be “Goals” in the formula field. If they say "I can't stick to goals" and "I'm not great at speaking" it should return "Goals and Speaking." If they say "I can't stick to goals", "My habits and routines could be better", and "I'm not great at speaking" it should return "Goals, Habits and Speaking."

What I can get it to return is a response of “Goals, Habits, Speaking,”

I’m starting to think that’s not possible, and just want to see if maybe I’m wrong. 

Here’s what I have for a formula currently.

 

REGEX_REPLACE(
  REGEX_REPLACE(
    TRIM(
      IF(FIND("I can't stick to goals", ARRAYJOIN({Weaknesses}, ",")), "Goals, ", "") &
      IF(FIND("I'm not great at speaking", ARRAYJOIN({Weaknesses}, ",")), "Speaking, ", "") &
      IF(FIND("My habits and routines could be better", ARRAYJOIN({Weaknesses}, ",")), "Habits, ", "") &
      IF(FIND("I have no other people involved", ARRAYJOIN({Weaknesses}, ",")), "People, ", "") &
      IF(FIND("I can't remember vocabulary", ARRAYJOIN({Weaknesses}, ",")), "Vocabulary, ", "") &
      IF(FIND("My motivation is not stable", ARRAYJOIN({Weaknesses}, ",")), "Motivation, ", "") &
      IF(FIND("I struggle to enjoy reading", ARRAYJOIN({Weaknesses}, ",")), "Reading, ", "") &
      IF(FIND("I'm overwhelmed by listening", ARRAYJOIN({Weaknesses}, ",")), "Listening, ", "") &
      IF(FIND("I can't do grammar", ARRAYJOIN({Weaknesses}, ",")), "Grammar, ", "") &
      IF(FIND("Balance isn't my strength", ARRAYJOIN({Weaknesses}, ",")), "Balance, ", "") &
      IF(FIND("My writing could be better", ARRAYJOIN({Weaknesses}, ",")), "Writing, ", "") &
      IF(FIND("I'm disconnected from the culture", ARRAYJOIN({Weaknesses}, ",")), "Discovery, ", "")
    ),
    ", $", ""
  ),
  ", ( ^,]+)$", " and $1."
)


The mapping for the multi select answer to the 1 word response is here:

I can't stick to goals = Goals
I'm not great at speaking = Speaking
My habits and routines could be better = Habits
I have no other people involved = People
I can't remember vocabulary = Vocabulary
My motivation is not stable = Motivation
I struggle to enjoy reading = Reading
I'm overwhelmed by listening = Listening
I can't do grammar = Grammar
Balance isn't my strength = Balance
My writing could be better = Writing
I'm disconnected from the culture = Discovery


Any proper formula, or confirmation that I truly cannot use a formula in Airtable to get the return grammatically correct would be appreciated. Thank you!

How does this look?

REGEX_REPLACE(
  REGEX_REPLACE(
    REGEX_REPLACE(
      REGEX_REPLACE(
        REGEX_REPLACE(
          REGEX_REPLACE(
            REGEX_REPLACE(
              REGEX_REPLACE(
                REGEX_REPLACE(
                  REGEX_REPLACE(
                    REGEX_REPLACE(
                      REGEX_REPLACE(
                        REGEX_REPLACE(
                          TRIM(
                            ARRAYJOIN({Weaknesses}, ", ")
                          ),
                          "I can't stick to goals", "Goals"
                        ),
                        "I'm not great at speaking", "Speaking"
                      ),
                      "My habits and routines could be better", "Habits"
                    ),
                    "I have no other people involved", "People"
                  ),
                  "I can't remember vocabulary", "Vocabulary"
                ),
                "My motivation is not stable", "Motivation"
              ),
              "I struggle to enjoy reading", "Reading"
            ),
            "I'm overwhelmed by listening", "Listening"
          ),
          "I can't do grammar", "Grammar"
        ),
        "Balance isn't my strength", "Balance"
      ),
      "My writing could be better", "Writing"
    ),
    "I'm disconnected from the culture", "Discovery"
  ),
  ", ([^,]+)$", " and $1."
)

 

Hey ​@Cole_Wedemeier!

Solution provided by Adam above seems to work great!!! However, if that is not the case, just let us know and we’ll dig deeper into it!

Mike, Consultant @ Automatic Nation 

​@TheTimeSavingCo That’s amazing!  Thank you so much!

I realize I mistyped and it should have a comma before the “and” but that was easily fixed. 

I love this community and the quick friendly brilliance I always find. This time is no exception. Thank you again.

Reply


Terms & Conditions