Find and replace part of an item in a SharePoint List

[Nic_ol]

Hello,

I am very new to SharePoint and have inherited a long SharePoint List which needs multiple items updated.

What I need to do is find all the items in a Column containing the term "ER" in part of the entry and replace this part of the entry with the term "EAR". In Excel, this is easily done using the 'Find and Replace' search function. Is there a simple way of finding and replacing part of an item entry quickly in a SharePoint List?

Here is an example of items that I currently have and what I want to change them to:

Current Items:
14ER-110
16ER-215
18ER-330

After updating, the Items should read:
14EAR-110
16EAR-215
18EAR-330

Thank you 🙂

[#2]

@Nic_ol 

I thought a Flow might be the easiest way, but honestly, you can just utilize a view and the grid view to update the items. Unless you are talking insane amounts of items. You can create a view, use edit view, and under filter, choose the column then contains and ER. Save and then you get a list of all ER for that column. You can click edit in grid view, then you could if the list fits on the page (100?) you can copy that entire column of data out to excel, do you're replace, then copy the data and paste it over the columns. You could easily create a new column next to the existing one to hold those values as a test to make sure it works as well before trying that change. 

You could do this in chunks with the view as well if there are lots of items or you're other alternitive is utilizing flow, and a combo of get items, a condition with update item action using replace expression. 

[#3]

Thank you Chris, your advice has been very helpful (apologies for delayed reply- only just returned from holiday break).

There were only 101 items with the ER term, so I tried the New View, Filter, Export the column into Excel, Find and Replace and then Copy and Paste back into Sharepoint List- this worked (needed to use Edge browser to do the final paste back into Sharepoint List).

Thank you for the Flow tips too. I am still keen to know how to do this in Flow (as I can see this being needed in future applications), but I have very little experience with Flow as yet. If you know of any further resources for teaching a beginner how to set up a one-off find and replace flow such as this, I would be grateful for sharing,

[#4]

Hi Chris, I've searched and searched for a Flow that will do exactly what Nicole is asking. Do you happen to have a link to an example or could you possibly provide one. It seems that would be a frequent request but I can't find something, seemingly, that simple.

Trying to figure it out myself, I can find a "Get Items" but then when I get to the update part, it only offers me Update ITEM. Confusing.

Thank you.

[#5]

@CindyZ you will rarely find a flow that does exactly what you want, it's usually quicker & easier just to build one from blank. And this is not a difficult flow.

I have a SharePoint list with the data provided in the original post:

The trigger is to manually trigger the flow. Then the first action is a SharePoint get items. I've added a filter query to only bring back items that have a value:

Add an apply to each and select value from the dynamic content. Then add a compose action and select the column which has the data you want to change, in my casre it's the Title column.

Next, add another compose action and in the expression tab of the dynamic content box add the following expression:

replace(outputs('ComposeTitle'),'ER','EAR')

That takes the ComposeTitle compose and replaces ER with EAR:

Finally and still inside the apply to each, add an update item and for the column with the changed data select the output of the previous compose:

 And this is the result:

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#6]

@RobElliott Perfect, Rob!  Thanks so very much.  I'm sure this will help lots of others.  And thanks for the affirmation you can seldom find an exact flow.  Sometimes it feels that I must be missing the obvious but don't know what I don't know.  🙂  

[#7]

@RobElliott 

I'm looking to do a similar thing, but am having issues with the Flow (full disclosure--I've never used Flows).

I have a Notes column and want to mass edit changing an error from when data was imported into the list (user did a find/replace in the .CSV file and it was a tad too aggressive, changing things we didn't want to change).

I was able to create the new flow as shown above, and I selected my site address and selected my List in the List Name, however when I click "add dynamic content" it doesn't show any of the columns I have in my list--it just has general fields like name, email, date, city, state, etc, few of which are present in this particular List.

[#8]

@Joe_Grover I can see from the cursor that you are in the filter query field. There won't be anything in the dynamic content box for that at this stage, the only items you have are in the manually trigger a flow section. So leave the filter query empty.

What you then need is a Filter Array action. Select value from the dynamic content box and you will then see in the filter field that the columns from your get items are available.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#9]

@Joe_Grover @Nic_ol @CindyZ 

In case if you don't want to "store" the replaced value in column (update actual column value) and fine with showing replaced string in list view (display purpose only - filter/sort will use old values only), you can also replace the substring in text string using SharePoint JSON formatting.

Check this article for detailed information and example: SharePoint: Replace All Occurrences of Substring in a String using JSON Formatting 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#10]

Ok, thanks. That got me a bit further. I'm almost there, heh.

I need to tweak the steps a bit for what I'm trying to do.

- "ComposeTitle" shows as an invalid reference in my Compose action. The field I want to search for text in is called "Notes," so what would I put in for my expression in the Compose action? For clarity I want to search for a string of text (irrespective of case) and change one of the words. For example, looking for "this is good" and changing it to "this is great".

- In the Update item action, do I add the Outputs to the Notes field?

- And there are some required field in the form. I don't know if I can ignore the warnings that the fields are required (since I don't want to touch those fields) or if I should temporarily disable that requirement in order to run the Flow. This wouldn't be a big deal as nobody should be entering/changing stuff in this List anymore anyway--there just isn't a way to mass-change this after we migrate the data so I'd like to correct it beforehand.

[#11]

I did not have an account on this website and I took the time to create it just to thank you for sharing this flow, you have no idea of the incredible benefit I got from it, thank you very much

[#12]

@Nic_ol 
No need for a flow, just do the following:

• filter by the value you want to update
• Select all
• Click on edit
• Enter the new value in the edit form in the corresponding cell