Separating Intials

brian.wood1991

How do I separate initials of up to six names into an abbreviation? For example, I want to breakdown Juan Pablo Ramon Gutierrez Jr into JPRGJ. There will always only be a space in the middle with no hyphens or other delimiters but I am struggling with how to get above 3. This formula has gotten me to three:

=IF(LEN([Product 2]3) - LEN(SUBSTITUTE([Product 2]3, " ", "")) = 1, LEFT([Product 2]3, 1) + "" + MID([Product 2]3, FIND(" ", [Product 2]3) + 1, 1), LEFT([Product 2]3, 1) + "" + MID([Product 2]3, FIND(" ", [Product 2]3) + 1, 1) + IFERROR("" + MID([Product 2]3, FIND(" ", [Product 2]3, FIND(" ", [Product 2]3) + 1) + 1, 1), ""))

jmyzk_cloudsmart_jp

Hi @brian.wood1991

Not an elegant approach, but it works for unlimited initials, and one with a limited number of initials, handling special accent characters.

https://app.smartsheet.com/b/publish?EQBCT=0fa7bdb9a8a841eebbfdd81d42e9ab26

[nested SUBSTITUTE] =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Product 2]@row , "-", " "), "a", ""), "b", ""), "c", ""), "d", ""), "e", ""), "f", ""), "g", ""), "h", ""), "i", ""), "j", ""), "k", ""), "l", ""), "m", ""), "n", ""), "o", ""), "p", ""), "q", ""), "r", ""), "s", ""), "t", ""), "u", ""), "v", ""), "w", ""), "x", ""), "y", ""), "z", ""), " ", "")

[Up to 5 initials] =UPPER(LEFT([Product 2]@row , 1) + MID([Product 2]@row , FIND(" ", [Product 2]@row ) + 1, 1) + MID([Product 2]@row , FIND(" ", [Product 2]@row , FIND(" ", [Product 2]@row ) + 1) + 1, 1) + MID([Product 2]@row , FIND(" ", [Product 2]@row , FIND(" ", [Product 2]@row , FIND(" ", [Product 2]@row ) + 1) + 1) + 1, 1) + MID([Product 2]@row , FIND(" ", [Product 2]@row , FIND(" ", [Product 2]@row , FIND(" ", [Product 2]@row , FIND(" ", [Product 2]@row ) + 1) + 1) + 1) + 1, 1))

or

=SUBSTITUTE(

  SUBSTITUTE(

    SUBSTITUTE(

      SUBSTITUTE(

        SUBSTITUTE(

          SUBSTITUTE(

            SUBSTITUTE(

              SUBSTITUTE(

                SUBSTITUTE(

                  SUBSTITUTE(

                    SUBSTITUTE(

                      SUBSTITUTE(

                        SUBSTITUTE(

                          SUBSTITUTE(

                            SUBSTITUTE(

                              SUBSTITUTE(

                                SUBSTITUTE(

                                  SUBSTITUTE(

                                    SUBSTITUTE(

                                      SUBSTITUTE(

                                        SUBSTITUTE(

                                          SUBSTITUTE(

                                            SUBSTITUTE(

                                              SUBSTITUTE(

                                                SUBSTITUTE(

                                                  SUBSTITUTE(

                                                    SUBSTITUTE(

                                                      SUBSTITUTE(

                                                        [Product 2]@row, "-", " "),

                                                      "a", ""),

                                                    "b", ""),

                                                  "c", ""),

                                                "d", ""),

                                              "e", ""),

                                            "f", ""),

                                          "g", ""),

                                        "h", ""),

                                      "i", ""),

                                    "j", ""),

                                  "k", ""),

                                "l", ""),

                              "m", ""),

                            "n", ""),

                          "o", ""),

                        "p", ""),

                      "q", ""),

                    "r", ""),

                  "s", ""),

                "t", ""),

              "u", ""),

            "v", ""),

          "w", ""),

        "x", ""),

      "y", ""),

    "z", ""),

  " ", "")

=UPPER(

  LEFT([Product 2]@row, 1) +

  MID([Product 2]@row, FIND(" ", [Product 2]@row) + 1, 1) +

  MID([Product 2]@row, FIND(" ", [Product 2]@row, FIND(" ", [Product 2]@row) + 1) + 1, 1) +

  MID([Product 2]@row, FIND(" ", [Product 2]@row, FIND(" ", [Product 2]@row, FIND(" ", [Product 2]@row) + 1) + 1) + 1, 1) +

  MID([Product 2]@row, FIND(" ", [Product 2]@row, FIND(" ", [Product 2]@row, FIND(" ", [Product 2]@row, FIND(" ", [Product 2]@row) + 1) + 1) + 1) + 1, 1)

)