Formula to remove text after a comma

Um, that looks perfectly fine to me — that is, it does once you remove the quotation marks from around the second {Name of Field}:

 LEFT(
    {Name of field},
    FIND(
        ",",
        {Name of field}
        )-1
    )

 LEFT(
    {Name of field},
    FIND(
        ",",
        {Name of field}
        )-1
    )

How can I do this to just remove the last coma in a list?
I have columns for each of our products, in each row we list how many of that item a customer ordered.

We have a summary column which uses a ton of IF statements to show “Product1qty, Product2qty, ect,”. I need to remove the final “,”. But I don’t know if product 1, 2, 3 or 50 will be the final product.

If the final comma is always the last character in the string,

LEFT({Summary},LEN({Summary})-1)

will do the job.

If it’s not the last character, there is a way to do it, as well — but I need to understand better what you’re trying to do. (I assume you don’t want something like “Product1-qty, Product2-qtyProduct3-qty”…)

That worked perfectly!!! Thank you for your help. I knew it was something simple that I was overlooking. I completely forgot about LEN! I don’t think I’ve ever needed to use that function before.

 LEFT(
    {Name of field},
    FIND(
        ",",
        {Name of field}
        )-1
    )

I have some SKU’s that look like that:

• MY-SKU1-COLOR1
• MY-SKU2-COLOR2
• MY-SKU3-COLOR3
• MY-SKU4-COLOR4
• MY-SKU10
• MY-SKU11
• MY-SKU12
• MY-SKU13

I need to only remove what’s after the second “-” include that character so the result will be:

• MY-SKU1
• MY-SKU2
• MY-SKU3
• MY-SKU4
• MY-SKU10
• MY-SKU11
• MY-SKU12
• MY-SKU13
  Those SKU’s that don’t have a second dash should not be affected.

This worked to remove everything after the first “-” - how can I do it do find only the second one?

LEFT(
{SKU},
FIND(
    "-",
    {SKU}
    )-1
)

LEFT(
{SKU},
FIND(
    "-",
    {SKU}
    )-1
)

Given these fields…

Like this maybe?

IF(FIND("-", MID({Raw SKUs}, FIND("-", {Raw SKUs}) + 1, 20)), MID(MID({Raw SKUs}, FIND("-", {Raw SKUs}) + 1, 20), 1, FIND("-", MID({Raw SKUs}, FIND("-", {Raw SKUs}) + 1, 20)) - 1), MID({Raw SKUs}, FIND("-", {Raw SKUs}) + 1, 20))

IF(
  FIND("-", 
    MID({SKU}, 
      FIND("-", {SKU}
      ) + 1, 20
    ) 
  ), 
    MID(
      MID({SKU}, 
          FIND("-", {SKU}
          ) + 1, 20
      ), 1, 
      FIND("-", 
            MID({SKU}, 
              FIND("-", {SKU}
              ) + 1, 20
            )
          ) - 1
    ), 
      MID({SKU}, 
        FIND("-", {SKU}
        ) - 2, 20
      )
)

This code helped me to get to show the proper SKU for only the SKU’s that had one single “-” in them. For those with two “-” it shows the second word only

https://dl.airtable.com/.attachments/b3696bd7198666c02911bcc45a6bbc2b/0b199979/ScreenShot2019-09-09at7.54.50PM.png

IF(
  FIND("-", 
    MID({SKU}, 
      FIND("-", {SKU}
      ) + 1, 20
    ) 
  ), 
    MID(
      MID({SKU}, 
          FIND("-", {SKU}
          ) + 1, 20
      ), 1, 
      FIND("-", 
            MID({SKU}, 
              FIND("-", {SKU}
              ) + 1, 20
            )
          ) - 1
    ), 
      MID({SKU}, 
        FIND("-", {SKU}
        ) - 2, 20
      )
)

Any way to improve it to show the desired SKU?:
https://dl.airtable.com/.attachments/b3696bd7198666c02911bcc45a6bbc2b/0b199979/ScreenShot2019-09-09at7.54.50PM.png

(In case you haven’t found a solution yet…)

The easiest way is to replace the second ‘-’ with another character and then perform a LEFT() based on that marker character. I typically use the vertical bar (’|’) as my marker, since it’s typically not found in text; if for some weird reason you have vertical bars in your SKUs, you’ll need to choose another non-alpha character as a marker. :winking_face:

This processes the sample SKUs and returns the desired values:

IF(
    LEN(
        {SKU}
        )-LEN(
            SUBSTITUTE(
                {SKU},
                '-',
                ''
                )
            )<=1,
        {SKU},
        LEFT(
            {SKU},
            FIND(
                '|',
                SUBSTITUTE(
                    {SKU},
                    '-',
                    '|',
                    2
                    )
                )-1
            )
    )

In brief, it first counts the number of hyphens in the SKU; SKUs with no hyphens or a single hyphen are returned in full. For SKUs containing 2 or more hyphens, the second hyphen is replaced with a vertical bar; afterwards, all the text up to, but not including, the bar is returned.

I used this formula in conjunction with a series of if(find()) statements. The if(find()) statement searches a multiple select field with 245 for 25 specific options.

IF(FIND(“US”, {Availability}), "US, ", “”) &
IF(FIND(“CA”, {Availability}), "CA, ", “”) &
IF(FIND(“AU”, {Availability}), "AU, ", “”) &
IF(FIND(“NZ”, {Availability}), "NZ, ", “”) &
IF(FIND(“GB”, {Availability}), "GB, ", “”) &
IF(FIND(“IE”, {Availability}), "IE, ", “”) &
IF(FIND(“AS”, {Availability}), "AS, ", “”) &
IF(FIND(“CC”, {Availability}), "CC, ", “”) &
IF(FIND(“CK”, {Availability}), "CK, ", “”) &
IF(FIND(“CX”, {Availability}), "CX, ", “”) &
IF(FIND(“FM”, {Availability}), "FM, ", “”) &
IF(FIND(“GG”, {Availability}), "GG, ", “”) &
IF(FIND(“GU”, {Availability}), "GU, ", “”) &
IF(FIND(“IM”, {Availability}), "IM, ", “”) &
IF(FIND(“JE”, {Availability}), "JE, ", “”) &
IF(FIND(“MH”, {Availability}), "MH, ", “”) &
IF(FIND(“MP”, {Availability}), "MP, ", “”) &
IF(FIND(“NF”, {Availability}), "NF, ", “”) &
IF(FIND(“NU”, {Availability}), "NU, ", “”) &
IF(FIND(“PR”, {Availability}), "PR, ", “”) &
IF(FIND(“PW”, {Availability}), "PW, ", “”) &
IF(FIND(“TK”, {Availability}), "TK, ", “”) &
IF(FIND(“VI”, {Availability}), "VI, ", “”) &
IF(FIND(“IN”, {Availability}), "IN, ", “”) &
IF(FIND(“PH”, {Availability}), "PH, ", “”)

There is then an automation which updates a second multiple select field based on the formula column generated. I’m using a comma at the end of the returned text in the formula in order to separate values, but want to remove the final comma so as not to create the empty value in the resulting multi-select.

I tried added the provided formula as an additional & LEFT({Availability},LEN({Availability})-1).

It did remove the final comma, but it also broke the if/find statements and rather than searching for the 25 specified values it began returning all 245 values. If it was one of the 25 above, it deleted the comma, but if it was one of the other values it returned the selection and deleted the last letter.

Thinking I might have combined it incorrectly with the if/find statements? Any help would be appreciated it.