Putting a space in a post code if one hasn't been entered using Power Automate

[Anonymous]

Afternoon

I have a list with a postcode field which over time has had users enter the postcodes with no spaces.

So the postcode have either 3 or 4 characters before where the space should be (e.g. TA1 or TA24) then all postcodes have 3 characters after the space. I need some kind of  condition to determine where to put the space based on the total number of characters in the string (if 6 characters then space would go after 3rd char vs if 7 characters, space would go after 4th character.

Any ideas the best way to achieve this? Thanks in advance.

[Expiscornovus]

Hi @Anonymous,

One approach could be to use an expression with functions like equals, length, substring and if.

Below is an example of that approach. In that example I am using two compose actions.

In the first compose action I add the postcode to test. The second compose action has the following expression added to it:

if(equals(length(outputs('Compose')), 6), concat(substring(outputs('Compose'), 0, 3), ' ', substring(outputs('Compose'), 3, 3)), concat(substring(outputs('Compose'), 0, 4), ' ', substring(outputs('Compose'), 4, 3)))

[Anonymous]

Thanks @Expiscornovus I'll give that a go!

[abm · Accepted solution]

Hi @Anonymous 

Here it is

The expression I used above is as follows:

if(equals(length(outputs('Compose')),6), concat(substring(outputs('Compose'),0,3), ' ', substring(outputs('Compose'),3,3)), concat(substring(outputs('Compose'),0,4), ' ', substring(outputs('Compose'),4,3)))

[Anonymous]

Thanks @abm  would I need a Condition action after that and an update list item action?

[abm]

Hi @Anonymous 

No you don't need. See my expression I did and here I am already checking whether its 6 or not?

Try the solution which I suggested. 

Thanks

[abm]

Add a compose. Map the postcode under that. Then next compose copy my expression which I suggested. I have tested this and worked as expected.

[Anonymous]

Thanks @abm  I'll give it a go!

[abm]

There was a typo earlier. I have corrected it.

[Anonymous]

Thanks @abm it looks like it's working. So what's the best way to update the Post Code field in the SP list to show the postcode with the space?

[abm]

Hi @Anonymous 

Use the SharePoint connector Update Item action step and map the field. 

[OliverR-82]

If the goal is to go over each item in the list and make the necessary corrections, then I would suggest an instant cloud flow that gets all the items from the list and loops over each one, updating the postcode. Below is a framework of what the flow would look like. I've not taken the time to actually fill in any existing SP sites or anything, but it should give you an idea.

In the Update Item action, you would insert the output from the Compose action in the Postcode field.

EDIT: you would also have to change the formula of the Compose action, of course, to work with the actual postcode field of the item and not with a previous compose as you have done in your tests. So, in your formula you would replace outputs('Compose') with outputs('Get_items')?['postcode'], for example, assuming the column name of the list is 'postcode'.

EDIT 2: I also strongly advise to test this with a test list first, to make sure it works as intended. This will update each item in your list so you want to do it right the first time.

[Anonymous]

Thanks @abm  I was adding that action while you were typing, but an odd thing I'm getting is that after it's added the space the Flow is erasing the last three characters. Could I change the expression so that it added a space three characters in? 

[abm]

Not sure I understood. What you are saying is flow is truncating the values? That means the solution I have give is based on length 6 or 7 postcode types. Sounds like you have some post codes with other lengths as well? Is that correct?

[Anonymous]

yES, 

@abm I just put T23456 and T234567 in on the postcodes fields. After initially putting the space in, the characters after the space get erased, so I'm left with T23 and T234.

[abm]

Hi @Anonymous 

Check your compose expression values from run history? See what values are returned? 

[Anonymous]

@abm I can see what it's doing. After it's successfully running the first flow to put in the space for some reason it's firing again and deleting a character until for example with a six character postcode it stops with the first three characters!

[OliverR-82]

What do you mean "it's firing" again? What is the trigger action for your flow?

[abm]

Hi @Anonymous 

Could you please show a screenshot of your flow and run history? Try to expand all the steps in your screen shot.

Thanks

[Anonymous]

 Hi @abm . I the trigger up as a 'created or modified' just to test out and then put a condition in which stopped it after the first go round. For production I'm going to use the button as suggested earlier. Thanks everyone for your help on this! Appreciated!