Re: Formula to remove text after a comma

Muriel_Vanderme · ‎Mar 20, 2018

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!

W_Vann_Hall · ‎Mar 20, 2018

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
    )

Kira_Arneson1 · ‎Jun 06, 2019

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.

W_Vann_Hall · ‎Jun 06, 2019

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

Kira_Arneson1 · ‎Jun 06, 2019

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.

Nachumi_Eliyahu · ‎Sep 09, 2019

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
)

Bill_French · ‎Sep 09, 2019

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

Nachumi_Eliyahu · ‎Sep 09, 2019

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

Nachumi_Eliyahu · ‎Sep 10, 2019

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

W_Vann_Hall · ‎Sep 24, 2019

(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.