How to Find and Replace All for Special Characters in a SharePoint list?

(0) Share

Report

Posted on by Australia

387

Hi all,

Does anyone know if we can create a PA flow to perform 'Find and Replace All' in a SharePoint list?

In my list, I have hundreds of strings that contain certain special characters. e.g. "A New World#*^s Perspective".

How to find every #*^ in the list and replace all with an apostrophe ' so it becomes "A New World's Perspective"?

Thank you in advance.

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

eliotcole 4,363 Moderator on at

Like (1)

Report

Would you perhaps be better off performing this work in SharePoint?

For example, I imagine that this is only happening in a few columns.

Perhaps the example column is a rich text, multi-line, column that absolutely doesn't need to be rich text, and could just be a plain text multi-line, column.

Once you change the format from Rich Text, SharePoint stops doing the HTML character conversions that it does, and should just make things a bit more 'proper'.

Equally, another example could be creating helper columns that are calculated columns based upon the value of previous column. That said, if this were the case there's a good chance they don't work on rich text values. But still.

Was this reply helpful? Yes No
Australia 387 on at

Like (1)

Report

@eliotcole Thanks for the reply.
Unfortunately I have about 500 cells that contain these symbols, so am not looking forward to a manual process on this. 😞 The column is currently set as text/string not rich text.

I would not want to re-upload the list either as it is very large (10,000+ rows) and currently being used by several Power Apps.

Was this reply helpful? Yes No
Verified answer

eliotcole 4,363 Moderator on at

Like (1)

Report

Apologies, I'm not suggesting 'manual' editing in SharePoint, I'm suggesting using calculated columns, etc.

Also, I may be misremembering, but I thought certain changes in column type maintained the data within. I could be wrong, though.

However, if you're insistent on using PA, then what I would recommend, here, is; to run a Get items action on the list, and use an ODATA filter in the action to filter for the various phrases (like "#*^").

This would cut down the amount of actions that the flow needs to do and how long it will process for.

Once you have that list data, it's just a matter of running an Apply to each on each item in there, and simply either run a single action in there (Update item) with the replace actions in an expression or two, or run a couple of extra actions to sift through with maybe a Switch, then Update item after that.

Was this reply helpful? Yes No
Australia 387 on at

Like (1)

Report

Many thanks @eliotcole
I think I just worked out a work around which achieved the goal I wanted.

Steps:
Export the SharePoint List and identify those have the weird symbols.
Fix these rows in a new Excel spreadsheet (use Find and Replace All)
Upload this Excel file to SharePoint folder
Use List rows present in a table > Apply to Each > Get Items > Apply to Each 2 > Update Item

This method seems to have worked.
Once again, thanks again for your help 🙂

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report

glad you're sorted, either way!

Was this reply helpful? Yes No