Text to Column Between Delimiters but maintain text without delimiters

[mcash]

How can I extract the text between comma delimiters while still keeping text with no delimiters? Below is an example of what I am starting with (Before) and the text I want to keep (After).

Before	After
Service Charge	Service Charge
Service Charge	Service Charge
TX, Product 001, 220W	Product 001
LA, Product 001, 220W	Product 001
OK, Product 001, 220W	Product 001

[Greg_Deckler]

@mcash Try:

After Column = 
    IF(
        NOT(CONTAINSSTRING([Before],",")),
        [Before],
                VAR __First = FIND(",",[Before])
                VAR __Second = FIND(",",[Before],__First+2)
                VAR __Result = MID([Before],__First + 2, __Second - __First - 2)
            RETURN
                __Result
    )

[mcash]

@Greg_Deckler I am receiving an error.

"The search Text provided to function 'FIND' could not be found in the given text."

[Greg_Deckler]

@mcash You must have some rows with just a single comma, try:

After Column = 
    IF(
        NOT(CONTAINSSTRING([Before],",")),
        [Before],
                VAR __First = FIND(",",[Before])
                VAR __Second = FIND(",",[Before],__First+2, -1)
                VAR __Result = 
                    IF(
                        __Second > 0,
                        MID([Before],__First + 2, __Second - __First - 2),
                        MID([Before],__First + 2, LEN([Before]) - __First - 1)
                    )
            RETURN
                __Result
    )

[mcash]

This works well, but in my giant list of products there are some that do not have a space after the first comma; i.e. TX,Product 001 so it returns it as "roduct 001". Getting closer haha!

[Greg_Deckler · Accepted solution]

@mcash Including sample data that represented all of your edge cases would have saved a lot of back and forth and time wasted.

After After = 
    IF(
        NOT(CONTAINSSTRING([Before],",")),
        [Before],
                VAR __Before = SUBSTITUTE([Before],", ",",")
                VAR __First = FIND(",",__Before)
                VAR __Second = FIND(",",__Before,__First+1, -1)
                VAR __Result = 
                    IF(
                        __Second > 0,
                        MID(__Before,__First + 1, __Second - __First - 1),
                        MID(__Before,__First + 1, LEN([Before]) - __First - 1)
                    )
            RETURN
                __Result
    )

[mcash]

This works, thank you. I understand there is a lot of back and forth, but my data set is hundreds of thousands of rows long and I cannot necessarily capture every scenario and did not know that a space or no space mattered; I'm very novice.

Thank you again for your help.