Removing full stops from a number sequence

%3CLINGO-SUB%20id%3D%22lingo-sub-2712652%22%20slang%3D%22en-US%22%3ERemoving%20full%20stops%20from%20a%20number%20sequence%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712652%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20a%20single%20line%20text%20column%20that%20we%20use%20to%20ID%20folders.%20The%20ID's%20follow%20this%20structure%201.1.2.1.2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20a%20calculated%20field%20can%20take%20this%20sequence%20and%20remove%20the%20full%20stops%20between%20the%20numbers%20so%20instead%20of%201.1.2.1.2%20we%20get%20a%20calculated%20column%20that%20returns%2011212%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2712652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECalculated%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EColumns%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716915%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20full%20stops%20from%20a%20number%20sequence%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811762%22%20target%3D%22_blank%22%3E%40jamiew23%3C%2FA%3E%26nbsp%3Bone%20way%20to%20do%20this%20is%20to%20build%20a%20flow%20in%20Power%20Automate%20to%20strip%20out%20the%20dots%20and%20update%20the%20list%20with%20the%20result.%20In%20my%20example%20the%20original%20column%20(single%20line%20of%20text)%20with%20the%20folder%20name%20with%20dots%20is%20called%20Folder%20and%20the%20result%20without%20the%20dots%20(also%20single%20of%20text)%20is%20called%20Result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20List%20Settings%20you%20MUST%20make%20sure%20that%20Item%20Version%20History%20is%20switched%20on%20as%20we%20will%20be%20comparing%20the%20current%20version%20of%20the%20item%20with%20the%20previous%20version.%20The%20flow%20will%20fail%20if%20you%20don't%20turn%20this%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Power%20Automate%20the%20trigger%20is%20the%20SharePoint%20%22when%20an%20item%20or%20a%20file%20is%20modified%22%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-Flow.png%22%20style%3D%22width%3A%20642px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307935iEE52D3B1B6796727%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221-Flow.png%22%20alt%3D%221-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20action%20is%20%22get%20changes%20for%20an%20item%20or%20a%20file%20(properties%20only).%20Select%20the%20ID%20from%20the%20dynamic%20content%20box%20on%20the%20right%20when%20you%20click%20in%20the%20ID%20field.%20For%20the%20Since%20field%20this%20is%20where%20we%20will%20look%20at%20the%20current%20and%20previous%20versions%20so%20add%20an%20expression%20of%3CBR%20%2F%3E%3CBR%20%2F%3Esub(int(triggerOutputs()%3F%5B'body%2F%7BversionNumber%7D'%5D)%2C1)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222-Flow.png%22%20style%3D%22width%3A%20850px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307937iFCAEFA6E342A4470%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222-Flow.png%22%20alt%3D%222-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20initialize%20a%20string%20variable%20and%20in%20the%20Value%20field%20select%20the%20column%20with%20your%20current%20folder%20text%2C%20in%20my%20case%20that's%20called%20Folder%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%223-Flow.png%22%20style%3D%22width%3A%20640px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307936iEB594F6FD80871F3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%223-Flow.png%22%20alt%3D%223-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BNext%2C%20add%20a%20Condition%20and%20select%20the%20Has%20column%20changed%3A%20Folder%20and%20set%20it%20to%20is%20equal%20to%20true%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%224-Flow.png%22%20style%3D%22width%3A%20648px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307938iB12C74369C0812BD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%224-Flow.png%22%20alt%3D%224-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20red%20if%20no%20channel%20just%20leave%20it%20empty%20as%20we%20don't%20want%20anything%20to%20happen%20in%20the%20case.%20In%20the%20green%20if%20yes%20channel%20add%20a%20compose%20action%20and%20add%20the%20following%20expression%20which%20replaces%20the%20dots%20in%20the%20variable%20with%26nbsp%3B%20nothing%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Ereplace(variables(%3C%2FSPAN%3E%3CSPAN%3E'varReplace'%3C%2FSPAN%3E%3CSPAN%3E)%2C%3C%2FSPAN%3E%3CSPAN%3E'.'%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E''%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%225-Flow.png%22%20style%3D%22width%3A%20649px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307939i1655626DC30F2C74%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%225-Flow.png%22%20alt%3D%225-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BFinally%2C%20update%20the%20list%20item%20with%20the%20output%20of%20the%20compose%20action%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%226-Flow.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307940i59679599FC687350%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%226-Flow.png%22%20alt%3D%226-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BThis%20is%20the%20result.%20You%20should%20note%20that%20it%20can%20take%2020-30%20seconds%20for%20the%20flow%20to%20complete%20and%20update%20the%20list%20item%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%227-SP-list.png%22%20style%3D%22width%3A%20221px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307941iD4F495DE00953D12%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%227-SP-list.png%22%20alt%3D%227-SP-list.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

We have a single line text column that we use to ID folders. The ID's follow this structure 1.1.2.1.2

 

Is there a way a calculated field can take this sequence and remove the full stops between the numbers so instead of 1.1.2.1.2 we get a calculated column that returns 11212?

 

Thanks!

2 Replies

@jamiew23 one way to do this is to build a flow in Power Automate to strip out the dots and update the list with the result. In my example the original column (single line of text) with the folder name with dots is called Folder and the result without the dots (also single of text) is called Result.

 

In your List Settings you MUST make sure that Item Version History is switched on as we will be comparing the current version of the item with the previous version. The flow will fail if you don't turn this on.

 

In Power Automate the trigger is the SharePoint "when an item or a file is modified":

 

1-Flow.png

 

The first action is "get changes for an item or a file (properties only). Select the ID from the dynamic content box on the right when you click in the ID field. For the Since field this is where we will look at the current and previous versions so add an expression of

sub(int(triggerOutputs()?['body/{versionNumber}']),1)

2-Flow.png

 

Next, initialize a string variable and in the Value field select the column with your current folder text, in my case that's called Folder:

3-Flow.png

 Next, add a Condition and select the Has column changed: Folder and set it to is equal to true

4-Flow.png

 

In the red if no channel just leave it empty as we don't want anything to happen in the case. In the green if yes channel add a compose action and add the following expression which replaces the dots in the variable with  nothing:

 

replace(variables('varReplace'),'.','')

 

5-Flow.png

 Finally, update the list item with the output of the compose action:

6-Flow.png

 This is the result. You should note that it can take 20-30 seconds for the flow to complete and update the list item:

7-SP-list.png

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

 

 

 

 

 

Thank you for this I'll take a look.
I've found that this code takes off the first dot in a sequence but doesn't remove the others. Is there any way I can manipulate this code to remove the other dots?

=REPLACE(NumberColumn,SEARCH(".",NumberColumn),"1",""),