Forum Discussion

IvanS's avatar
IvanS
Icon for Helper V rankHelper V
4 years ago
Solved

Remove text string based on given text string from the list

Hi guys, I am approching you as I am stuck with replacing given set of characters from the text string. Let's say I have following table:   Detailed Location Consolidated Location New Yor...
Need Help
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this

    Consolidated location = 
    TRIM(
        SUBSTITUTE(
            MID([Detailed Location],1,
                FIND("(",[Detailed Location]&"("
                )-1
            ),
            "General",""
        )
    )
HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
4 years ago

How about a Power Query solution?

Replace Values in the column -  replace General with empty value

Then split the column by delimiter - using ( as a delimiter

IvanS's avatar
IvanS
Icon for Helper V rankHelper V
4 years ago

This is unfortunately not possible as the column Detailed Location can consists of characters "(" and ")". In fact, this is the warehouse name - I have just used the cities to simplify the table.

 

The warehouse name can looks like below:

- Warehouse name (ex. old name) (C1) (1) 

- Warehouse name (ex. old name) (C1) (2)