Removing white space after using the Replace() expression

[dwalker97]

I have a name: John Doe Ryan. I use [Replace(),string,'Doe','' ] to remove 'Doe' from the name. 

I am then left with John  Ryan (with an extra space in between). I then try to search for this name to match a John Ryan (one space), but the Contains condition is coming back as false as it is not an exact 'contains' match, as it picks up on the extra whitespace.

Is there a way to prevent this from happening in the Replace expression? Or is there a way around this when comparing both names in the Condition step?

Thanks

Daragh

[Alastair]

Im sure someone smarter than me could tell you how to achieve that easily with the replace function.

As an alternative you could do split string expression (via space key) and reference the first and third values of the split expression to build the name correctly. 

[dwalker97]

Thanks for the reply. The thing is, what I have posted is a very small example of the overall problem I have.

There may be cases where the name has 2, 3, 4 or 5 values within the name. So I cant just reference the first and third values.

Thanks!

[Alastair]

How would you know which names in their full name are needed?  You have the same issue there with both the replace and split function.  In terms of the split expression, if you know the position of the names that you care about you can dynamically select the name by position.  

[dwalker97]

@Alastair I have stored the names to remove in an array, lets call it 'ArrayNames'. In this array I have 10 names stored which I want to remove from a table of 1000 rows of different full names. I am correctly finding and replacing all the names from full names, but the replace string is what's causing the issue as it is inserting an extra white space. 

Im not sure about the split function as the position of the name to remove will be different for each name.

Thanks a lot for your help with this

[Alastair]

For your array of names to remove have you tried adding a space to the end of them?  

[Paulie78]

Check out this blog post I did, towards the bottom there is an example of exactly what you are trying to do:

https://www.tachytelic.net/2021/04/power-automate-regex/

[dwalker97]

I suppose that will do! 😋

Thanks for your help

[dwalker97]

Thanks for sharing this @Paulie78 

[eliotcole · Accepted solution]

Hi, @dwalker97  ... With no disrespect meant to the answers already given, I feel like you could possibly be putting a bandage over a wound that won't close, here. ( also, as an aside, @Paulie78 's regex stuff is brilliantly useful ! )

Solution

Assuming from your information you need to remove ANY name on the blacklist can be easily achieved with a filter:

• Firstly - The 'Filter array' action checks the individual words in the name.If a word is blacklisted it is removed.
• Secondly - The valid names are put back together again with spaces between them.

Blacklist Removal

Details (etc) - All ignorable

The variables there are:

• nameArrVAR - This is the name split into its separate parts.
• removalsArrVAR - This is the list of items unwanted in the names.

You will note that all the items in the removalsArrVAR are lower case, this is intentional, because it's unclear whether the input name will be capitalised or not, because mistakes can be made.

The expressions in there are:

1. nameArrVAR - split(triggerBody()['text'], ' ') - This splits everything that is separated by a space.
2. Filter array - toLower(trim(item())) - Purely for the check against the removalsArrVAR this takes the currently considered nameArrVAR item() and:
   1. as a precaution removes any errant spaces,
   2. and makes it lower case to enable the 'does not contain' functionality to work.
3. nameVAR - join(body('Filter_array'), ' ') - This takes each remaining item in the now filtered array, and joins them as a string using a space to separate each item.

This uses the good work done here.

==================

More Advice and Questions (you can ignore this if you just want an elegant solution)

In order to help you ensure your data is smooth as silk going forward, it would be good to know:

1. Patterns - Is there a pattern to the name removal? ( ie. is it always middle names? )
   1. If there's a pattern, does it account for abbherations? ( ie. hypenations, names with two surnames or first names, foreign names to your locale )
2. Origin - Where is the name coming from? ( ie. is it another resource as just a pure text field? is it an O365 profile? )
3. User Input? - Is this detail being input by a user directly?
   1. If so, are they internal staff, and do they have an account on file that you can automatically get this detail from?
   2. If not, is this being done in a web-form or PDF, and does it have a specific 'Name' field, like Cognito or Jot?
4. Four - Text

But I have made a few broad assumptions ( like we all are - again, no harshness intended ) on some of thatfrom this reply:

@ Alastair I have stored the names to remove in an array, lets call it 'ArrayNames'. In this array I have 10 names stored which I want to remove from a table of 1000 rows of different full names. I am correctly finding and replacing all the names from full names, but the replace string is what's causing the issue as it is inserting an extra white space. 

Im not sure about the split function as the position of the name to remove will be different for each name.

Thanks a lot for your help with this

I would personally say that you should think about the data you're handling perhaps a little more ( not to say that you haven't purely as something to mull over 🙂 )  and consider:

• Local "Data Protection" statutes on personal information.
• Handling different types of names.
• How the data is input.

Looking at those will perhaps make you consider different methods to how it's handled, which could make dealing with it in places like Power Automate much easier. I'm not saying that in a "address book in excel" sillyness statement, more just to help with how you process this kind of information. It's all useful in the end, after all. 🙂

[eliotcole · Accepted solution]

Solution

Assuming from your information you need to remove ANY name on the blacklist can be easily achieved with a filter:

• Firstly - The 'Filter array' action checks the individual words in the name.If a word is blacklisted it is removed.
• Secondly - The valid names are put back together again with spaces between them.

Blacklist Removal

[eliotcole · Accepted solution]

Details (etc) - All ignorable

[eliotcole · Accepted solution]

The variables there are:

[eliotcole · Accepted solution]

You will note that all the items in the removalsArrVAR are lower case, this is intentional, because it's unclear whether the input name will be capitalised or not, because mistakes can be made.

[eliotcole · Accepted solution]

The expressions in there are:

[eliotcole · Accepted solution]

This uses the good work done here.

[eliotcole · Accepted solution]

==================

More Advice and Questions (you can ignore this if you just want an elegant solution)

In order to help you ensure your data is smooth as silk going forward, it would be good to know:

1. Patterns - Is there a pattern to the name removal? ( ie. is it always middle names? )
   1. If there's a pattern, does it account for abbherations? ( ie. hypenations, names with two surnames or first names, foreign names to your locale )
2. Origin - Where is the name coming from? ( ie. is it another resource as just a pure text field? is it an O365 profile? )
3. User Input? - Is this detail being input by a user directly?
   1. If so, are they internal staff, and do they have an account on file that you can automatically get this detail from?
   2. If not, is this being done in a web-form or PDF, and does it have a specific 'Name' field, like Cognito or Jot?
4. Four - Text

But I have made a few broad assumptions ( like we all are - again, no harshness intended ) on some of thatfrom this reply:

@ Alastair I have stored the names to remove in an array, lets call it 'ArrayNames'. In this array I have 10 names stored which I want to remove from a table of 1000 rows of different full names. I am correctly finding and replacing all the names from full names, but the replace string is what's causing the issue as it is inserting an extra white space. 

Im not sure about the split function as the position of the name to remove will be different for each name.

Thanks a lot for your help with this

I would personally say that you should think about the data you're handling perhaps a little more ( not to say that you haven't purely as something to mull over 🙂 )  and consider:

• Local "Data Protection" statutes on personal information.
• Handling different types of names.
• How the data is input.

Looking at those will perhaps make you consider different methods to how it's handled, which could make dealing with it in places like Power Automate much easier. I'm not saying that in a "address book in excel" sillyness statement, more just to help with how you process this kind of information. It's all useful in the end, after all. 🙂

[eliotcole · Accepted solution]

@ Alastair I have stored the names to remove in an array, lets call it 'ArrayNames'. In this array I have 10 names stored which I want to remove from a table of 1000 rows of different full names. I am correctly finding and replacing all the names from full names, but the replace string is what's causing the issue as it is inserting an extra white space. 

Im not sure about the split function as the position of the name to remove will be different for each name.

Thanks a lot for your help with this

[Paulie78]

Further to my previous response, you could also simply do:

replace(replace(replace(replace(replace(replace(replace(outputs('Name'), '  ', ' '), '  ', ' '), '   ', ' '), '    ', ' '), '     ', ' '), '      ', ' '), '       ', ' ')

This will replace all instances from 2 whites spaces up to seven white spaces, with a single white space in one expression.

[eliotcole]

If you're going to stick with purely addressing the text, for sure, @Paulie78  ... but for the sake of minimalism, wouldn't this be more efficient?

trim(join(union(array(' '), split(outputs('Name'), ' ')), ' '))

1. split() - This creates an array entry for each entity in the name split by a space (including spaces).
2. union() - This adds the split() array to an array consisting of a single space, it then discards any other space entries.
3. join() - This makes a string by joining the entries in the union() array placing a space between them.
4. trim() - This deletes any spaces from the start and end of the join() string.

---

@Paulie78 wrote:

Further to my previous response, you could also simply do:

replace(replace(replace(replace(replace(replace(replace(outputs('Name'), ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' ')

This will replace all instances from 2 whites spaces up to seven white spaces, with a single white space in one expression.

---

[Confused61]

I've tried this suggested formula to remove unwanted "whitespace" from within the text, which has had carriage return(newline) removed plus trimmed for whitespace both ends. It has no apparent effect, as I still had extended gaps between some text strings.

Perhaps those are caused by something else, like 'paragraph ghosts' or similar? 🤔