Forum Discussion

mcash's avatar
mcash
Icon for Helper I rankHelper I
3 years ago
Solved

Text to Column Between Delimiters but maintain text without delimiters

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

 

BeforeAfter
Service ChargeService Charge
Service ChargeService Charge
TX, Product 001, 220WProduct 001
LA, Product 001, 220WProduct 001
OK, Product 001, 220WProduct 001

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
    )

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      mcash
      Icon for Helper I rankHelper I
      3 years ago

      Greg_Deckler I am receiving an error.

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

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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
    )