Replace text using wildcard

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,598
Office Version
  1. 365
Platform
  1. Windows
I have a column of text that contains some text and example of which is below
Bloggs Joe, GB Sales:Required;Smith John, GB Sales:Required;Jones Sally, GB ASC:Required;Walker, Alan, GB Sales:Required

There could be a number of combinations of the 'GB ?:Required' text and I won;t know what these text strings will be

Basically what I need to do is just have the column contain the names so the above would be as follows
Bloggs Joe, Smith John, Jones Sally, Walker, Alan

Sometimes there will only be one name as follows
Bloggs Joe, GB Sales:Required

For this I would only need
Bloggs, Joe
to be in the column
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Does this do what you want?


Code:
let
    Source = "Bloggs Joe, GB Sales:Required;Smith John, GB Sales:Required;Jones Sally, GB ASC:Required;Walker, Alan, GB Sales:Required",


    fnGetNames = (S as text) as text =>
        let
            MidTxt = Text.BetweenDelimiters( S, "GB ", ":Required;",0,0),
            ReplcText = if 
                            MidTxt = "" 
                        then 
                            S 
                        else
                            @fnGetNames( Text.Replace(S, "GB " & MidTxt & ":Required;","") )
        in
            ReplcText,
                        
    Test = fnGetNames( Source & ";" )
in
    Test
 
Upvote 0
Plugged that in and it works a treat! Thanks!

The only thing I need to do then is to remove the last column. Is it as simple as replacing the last character with ""?
 
Upvote 0
When I try to look at this in Power Pivot the query is failing to load and I'm getting a stack overflow error.

Will this eat up a lot of memory?
 
Upvote 0
46k rows.

The message I get is -
Excel couldn't refresh the table 'CRM' from connection 'Query - CRM'. Here;s the error messages from the external data source:

The operation failed because the database does not exist, the source table doesn't not exist or because you do not have access to the data source.

More Details:
OLE DB or ODBC error:Evaluation resulted in a stack overflow and cannot continue.
An error occurred while processing table 'CRM'.
The current operation was cancelled because another operation in the transaction failed.
 
Upvote 0
Hello mikeymay, I have tested it on 46k rows and it seems fine on my side. How are you calling the function? Can you show us the M code?
 
Upvote 0
I'm new to PQ so I may not have done this properly...

In the Power Query Editor window, I clicked on the Add Column tab, then Custom Column and then added the code you supplied changing the static, names/text to the column it needs to read so it looks like this in the PQE window
Code:
= Table.AddColumn(#"Added Custom1", "New Part", each let
    Source = [Participant],


    fnGetNames = (S as text) as text =>
        let
            MidTxt = Text.BetweenDelimiters( S, "GB ", ":Required;",0,0),
            ReplcText = if 
                            MidTxt = "" 
                        then 
                            S 
                        else
                            @fnGetNames( Text.Replace(S, "GB " & MidTxt & ":Required;","") )
        in
            ReplcText,
                        
    Test = fnGetNames( Source & ";" )
in
    Test)
I close & Load and I can see the new column when I go to manage the model but in Excel when it tries to download, I get the error.
 
Last edited:
Upvote 0
you need to do something like this (Your M code has to end like this, after Added Custom 1


Code:
fnGetNames = (S as text) as text =>
        let
            MidTxt = Text.BetweenDelimiters( S, "GB ", ":Required;",0,0),
            ReplcText = if 
                            MidTxt = "" 
                        then 
                            S 
                        else
                            @fnGetNames( Text.Replace(S, "GB " & MidTxt & ":Required;","") )
        in
            ReplcText,

    NewTable = Table.AddColumn(#"Added Custom1", "New Part", each fnGetNames ( [Participant] & ";" ), type text)
in
    NewTable
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top