Hello, I know that SUBSTITUTE function doesn't support wildcards (such a shame), however I need to combine it somehow with SEARCH function to achieve my goal./ So the problem is like this: Existing columnNew columnThis is Not_a_Product T001This is Not_a_ProductThis truly is Not_a_Product T002This truly is Not_a_ProductThis is Not_a_Product T003This is Not_a_ProductProduct T001Product T001This truly is Not_a_Product TTTTTTTThis truly is Not_a_Product So if a "Not_a_Product" text is found, then replace everything AFTER this text with " " Thanks

Finally did it! Cleaned Column =SWITCH ( TRUE (), SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000, LEFT ( 'Table (2)'[Activity Name], LEN ( 'Table (2)'[Activity Name] ) - ( LEN ( 'Table (2)'[Activity Name] ) - SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) + 1 ) ), SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000, LEFT ( 'Table (2)'[Activity Name], LEN ( 'Table (2)'[Activity Name] ) - ( LEN ( 'Table (2)'[Activity Name] ) - SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) + 1 ) ), SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000, LEFT ( 'Table (2)'[Activity Name], LEN ( 'Table (2)'[Activity Name] ) - ( LEN ( 'Table (2)'[Activity Name] ) - SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) + 1 ) ), SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000, LEFT ( 'Table (2)'[Activity Name], LEN ( 'Table (2)'[Activity Name] ) - ( LEN ( 'Table (2)'[Activity Name] ) - SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) + 1 ) ), SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000, LEFT ( 'Table (2)'[Activity Name], LEN ( 'Table (2)'[Activity Name] ) - ( LEN ( 'Table (2)'[Activity Name] ) - SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) + 1 ) ), SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000, LEFT ( 'Table (2)'[Activity Name], LEN ( 'Table (2)'[Activity Name] ) - ( LEN ( 'Table (2)'[Activity Name] ) - SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) + 1 ) ), 'Table (2)'[Activity Name])

Sab

Helper V

5 years ago

Solved

SUBSTITUTE text found using wildcards

Hello,

I know that SUBSTITUTE function doesn't support wildcards (such a shame), however I need to combine it somehow with SEARCH function to achieve my goal./

So the problem is like this:

Existing column	New column
This is Not_a_Product T001	This is Not_a_Product
This truly is Not_a_Product T002	This truly is Not_a_Product
This is Not_a_Product T003	This is Not_a_Product
Product T001	Product T001
This truly is Not_a_Product TTTTTTT	This truly is Not_a_Product

So if a "Not_a_Product" text is found, then replace everything AFTER this text with " "

Thanks

Need Help

Sab
5 years ago
Finally did it!

Cleaned Column =
SWITCH (
    TRUE (),
    SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    'Table (2)'[Activity Name]
)

9 Replies

Sab
Helper V
5 years ago
btw I need this to be done using DAX
Fowmy
Super User
5 years ago
Sab

Add the following column:

New Column = var _tlength = LEN("Not_a_Product") var _tfoundat = SEARCH("Not_a_Product",TRIM(Table11[Existing column]),1,0) var _result = LEFT(TRIM(Table11[Existing column]),_tfoundat+_tlength) return _result

________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
YouTube LinkedIn
- Sab
  Helper V
  5 years ago
  Hi Fowmy
  
  Thanks for your reply.
  
  This works but the thing is now that I have multiple keywords with different length (so 'Not_a_Product' is just one of them)
  
  Sorry I forget to mention this in the original message 😞
Fowmy
Super User
5 years ago
Sab

Provide example and expected output please.

________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
YouTube LinkedIn
- Sab
  Helper V
  5 years ago
  Cleaned column =
  SUBSTITUTE (
  SUBSTITUTE (
  SUBSTITUTE (
  SUBSTITUTE (
  SUBSTITUTE (
  SUBSTITUTE ( TableName[ColumnName], "Keyword1", "" ),
  "Keyword2",
  ""
  ),
  "Keyword3",
  ""
  ),
  "Keyword4",
  ""
  ),
  "Keyword5",
  ""
  ),
  "Keyword6",
  ""
  )
  
  So this is the solution I created to remove Keywords from the columns, but now the thing is that some of the keywords have additional text after them that needs to be removed, for example:
  
  Keyword1 T01
  Keyword1 T34
  Keyword1 T22
  
  So my formula Substitues only Keyword1 to Keyword6 with " ", and I need it to remove everything after these Keywords
Fowmy
Super User
5 years ago
Sab

Not clear, provide more clarity

________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
YouTube LinkedIn

Sab

Helper V

5 years ago

Some text Keyword1 T01	Some text
Some other text Keyword1 T02	Some other text
Some text Keyword1	Some text
Some other text Keyword1 T04	Some other text
Some text Keyword1	Some text
Some other text Keyword1 T06	Some other text
Some other text Keyword2 R0002	Some other text
Some text Keyword2 R0003	Some text
Someother text Keyword2	Someother text
Some text Keyword2 R0005	Some text
Sometext Keyword3	Sometext

So everything from the start of the Keywords needs to be removed

Sab
Helper V
5 years ago
Anyone please?
Sab
Helper V
5 years ago
Finally did it!

Cleaned Column =
SWITCH (
    TRUE (),
    SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        - SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) + 1
                )
        ),
    'Table (2)'[Activity Name]
)

Forum Discussion

SUBSTITUTE text found using wildcards

9 Replies

Related content

Dynamic date substitution

Use Wildcard Operator

substitute multiple values

CALCULATE: Substitute for wildcard or CONTAINS

wildcard slicer