Find and replace part of an item in a SharePoint List

Occasional Contributor

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 :)

5 Replies

@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. 

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,
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.

@Cindy Zalme 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:
0-SP-List.png

 

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:

1-Flow.png

 

 

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:

2-Flow.png

 

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:

3-Flow.png

 And this is the result:

4-SP-Result.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@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.  :)