“I can’t figure out how to replace a new line (‘\n’) or any other non-printable character in Power Automate expression.”
The Power Automate expression replace(…) doesn’t work as you might expect for some non-printable characters, e.g. new line. The first thing you’d probably try is to replace ‘\n’. But Power Automate won’t process it as a new line, it’ll process it as it is: \n string. Replace(‘string’, ‘\n’, ”) will replace only the substring \n in the whole string. If the substring is not found, it’ll not replace anything and the flow will continue. To replace a new line you must use the right ‘new line’ character in your flow.
Store ‘new line’ in a variable
One of the solutions is to store the ‘new line’ into a variable and then use that variable in the replace(…) expression. Initialize a String variable, and in the ‘Value’ just press Enter. That’ll create a variable with a character representing a ‘new line’.
You can then use this variable in the replace(…) expression to replace new lines, e.g. with a semicolon.
replace(outputs('Compose'),variables('var_newLine'),';')
Get the ‘new line’ from an expression
Another approach, if you don’t want to create an extra variable in your flow, is to get the ‘new line’ from an expression. You can get the ‘new line’ character by a conversion from its percent-encoded (Uri) value ‘%0A’ via decodeUriComponent(‘Uri value’) expression.
decodeUriComponent('%0A')Instead of initializing a variable, you use decodeUriComponent(…) directly in the replace(…) expression. The example below will replace new lines with a semicolon.
replace(outputs('Compose'),decodeUriComponent('%0A'),';')
Note: some files created in Windows use \r\n instead of just \n as the ‘new line’ value. If you end up with \r in your string in place of the new lines, try to replace the whole \r\n substring:
decodeUriComponent('%0D%0A')Summary
Since this blog’s motto is “From no-code to low-code”, my preference is the expression. It’s not only reducing number of variables, but I’d consider it even safer to manage (“Why has the variable no value? I think I’ll delete it…”).
Also, the expression is not limited only to the ‘new line’ character. You can use the decodeUriComponent(…) for any problematic character, e.g. apostrophe. Instead of many apostrophes (as used e.g. in the Filter Query with apostrophes), you can decode the ‘%27’ value anywhere in your flow. You just need to find the percent-encoded value to replace.
Good tip! I’ve run into this before and ended up using the variable method, but the expression makes a lot of sense.
Great find. Wanted to share that if the output is from the Create CSV Table action then the following worked for me:
replace(body(‘Create_CSV_table’),decodeUriComponent(‘%0D%0A’),’;’)
The \r\n is successfully replaced with ‘;’
Utterly brilliant post that has finally helped me solve a problem that I’ve been trying to solve for hours. Thanks for helping everyone
Thanks! This saved a few hours of head scratching today.
Thanks! It works fine!
This was an unbelievable find. I had been searching for hours for a solution to this. We receive files from a vendor via FTP and we can’t process the data consistently because of the misc carriage returns in the files. I tried so many different methods, convert a CSV to Excel then back to CSV. Splitting the string into JSON, etc. etc. I just stumbled on this article and almost skimmed right past it because I couldn’t get the replace function to cooperate. I slowed down and read your solution and it worked PERFECTLY!!!!. Not only can I solve the issue with Power Automate, I can also solve it without needing to use any Premium features. Thanks so much for posting this.