Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to extract substring components from a parent string field

Hi

 

I have a column with many labels concatenated but separated by comma(,)

content looks like this

random text, CT: random text*, random text, random text

 

I need to extract the label that starts with "CT:" and ends with *.

 

I've solved this by having 3 columns. Is there an easier way or is it possible to do it in just one column?

 

Find start positon

A = FIND("CT:",'Campaign (1)'[Labels on Campaign],,BLANK())
Find end positon
D = FIND("*",'Campaign (1)'[Labels on Campaign],,BLANK())
Extract
CT = IF ( ISERROR ( MID ( 'Campaign (1)'[Labels on Campaign], 'Campaign (1)'[A], ('Campaign (1)'[D]-'Campaign (1)'[A]) ) ), BLANK (), MID ( 'Campaign (1)'[Labels on Campaign], 'Campaign (1)'[A], ('Campaign (1)'[D]-'Campaign (1)'[A]) ) )
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    try 

    CT =
VAR A = FIND("CT:",'Campaign (1)'[Labels on Campaign],,BLANK())
VAR D = FIND("*",'Campaign (1)'[Labels on Campaign],,BLANK())
RETURN
IF ( ISERROR ( MID ( 'Campaign (1)'[Labels on Campaign], A, (D-A) ) ), BLANK (), MID ( 'Campaign (1)'[Labels on Campaign], A, (D-A) ) )

    do not hesitate to give a kudo to useful posts and mark solutions as solution

1 Reply

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try 

    CT =
VAR A = FIND("CT:",'Campaign (1)'[Labels on Campaign],,BLANK())
VAR D = FIND("*",'Campaign (1)'[Labels on Campaign],,BLANK())
RETURN
IF ( ISERROR ( MID ( 'Campaign (1)'[Labels on Campaign], A, (D-A) ) ), BLANK (), MID ( 'Campaign (1)'[Labels on Campaign], A, (D-A) ) )

    do not hesitate to give a kudo to useful posts and mark solutions as solution