Extracting text

[gancw1]

I have a column that contains supplier names in 2 formats

Supplier Request for INX Corporation Pte Ltd on 03/09/2023 12:42 AM

Supplier Request for Vitcomm on 03/12/2023 06:11 AM

Supplier Registration for CEMIKA PTE. LTD.

Supplier Registration for CREATE LTD

What is the easiest way to extract the supplier name from this column  ?

[ichavarria · Accepted solution]

Hi @gancw1,

One possible approach to extract the supplier name from this column is to use Power Query in Excel or Power BI. Here are the steps:

1. Select the column that contains the supplier names.

2. Click on the "Transform data" button to open the Power Query Editor.

3. Click on the drop-down arrow next to the column header and select "Split Column" -> "By Delimiter".

4. In the "Split Column" dialog box, select "Custom" as the delimiter option and enter "for " (including the space after "for") as the delimiter text.

5. Click on "OK" to split the column into two separate columns.

6. Rename the new columns to something meaningful, such as "Action" and "Supplier".

7. Delete the "Action" column, as it is no longer needed.

8. Click on the drop-down arrow next to the "Supplier" column header and select "Trim" to remove any leading or trailing spaces.

9. Click on "Close & Load" to save the changes and return to the Excel or Power BI interface.
   
   

The resulting "Supplier" column should now contain the extracted supplier names. You may need to do some additional cleaning or formatting depending on the specific requirements of your analysis.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly

[gancw1 · Accepted solution]

I have figured out I can use the TextBetweenDelimiter() function to extract the supplier name by using "for " and " on" as start and end delimiters

= Table.AddColumn(#"Changed Type", "Supplier", each Text.BetweenDelimiters([Text], "for ", " from", 0, 0))

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Inserted Text Between Delimiters" = Table.AddColumn(Source, "Supplier name", each Text.BetweenDelimiters([Remarks], " ", " "), type text)
in
    #"Inserted Text Between Delimiters"

Hope this helps.

[gancw1]

I am not getting the same result. Asking for the text between 2 " " will return the 2nd word

#"Inserted Text Between Delimiters" = Table.AddColumn(Source, "Supplier name", each Text.BetweenDelimiters([Remarks], " ", " "), type text)

Can you share your PBIX file ?

[Ashish_Mathur]

Hi,

That M code got generated automatically when i used the "Column from Examples" feature.  Please use that feature.

[gancw1]

What did you enter in the example ?

[Ashish_Mathur]

The supplier name in the first 2 cells.

[ichavarria · Accepted solution]

Hi @gancw1,

One possible approach to extract the supplier name from this column is to use Power Query in Excel or Power BI. Here are the steps:

1. Select the column that contains the supplier names.

2. Click on the "Transform data" button to open the Power Query Editor.

3. Click on the drop-down arrow next to the column header and select "Split Column" -> "By Delimiter".

4. In the "Split Column" dialog box, select "Custom" as the delimiter option and enter "for " (including the space after "for") as the delimiter text.

5. Click on "OK" to split the column into two separate columns.

6. Rename the new columns to something meaningful, such as "Action" and "Supplier".

7. Delete the "Action" column, as it is no longer needed.

8. Click on the drop-down arrow next to the "Supplier" column header and select "Trim" to remove any leading or trailing spaces.

9. Click on "Close & Load" to save the changes and return to the Excel or Power BI interface.
   
   

The resulting "Supplier" column should now contain the extracted supplier names. You may need to do some additional cleaning or formatting depending on the specific requirements of your analysis.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly

[gancw1]

Thanks. Yes use split by delimiter twice, once with "for " and followed up with " from" will extract the supplier name