Formula to remove text after a comma

Hello,

Do you know what is the formula to remove text after a coma?

I tried to convert the Excel formula LEFT(A1,FIND(",",A1)-1) to something like LEFT({Name of field},FIND(",","{Name of field}")-1), but without success.

Thank you for your help!

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
    )
2 Likes

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”…)

1 Like

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.

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
)

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))

1 Like
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

1 Like

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