cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dwalker97
Helper I
Helper I

Removing white space after using the Replace() expression

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

 

dwalker97_0-1620398342017.png

 

 

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.

 

dwalker97_1-1620398390362.png

 

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
eliotcole
Continued Contributor
Continued Contributor

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 RemovalBlacklist 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. nameArrVARsplit(triggerBody()['text'], ' ') - This splits everything that is separated by a space.
  2. Filter arraytoLower(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. nameVARjoin(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. 🙂

View solution in original post

11 REPLIES 11
Alastair
Advocate I
Advocate I

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. 

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
Advocate I
Advocate I

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.  

@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
Advocate I
Advocate I

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

Paulie78
Super User III
Super User III

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/

 

I suppose that will do! 😋

 

Thanks for your help

Thanks for sharing this @Paulie78 

eliotcole
Continued Contributor
Continued Contributor

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 RemovalBlacklist 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. nameArrVARsplit(triggerBody()['text'], ' ') - This splits everything that is separated by a space.
  2. Filter arraytoLower(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. nameVARjoin(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. 🙂

View solution in original post

Paulie78
Super User III
Super User III

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
Continued Contributor
Continued Contributor

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.


 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Users online (2,391)