Forum Discussion
mcash
Helper I
Helper IText 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). Be...
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 )
Greg_Deckler
Community Champion
Community Championmcash 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
)
mcashHelper I
Helper IThis 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.