“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.
Thanks. This solved my issue…
Thank you so much. Like 6 hours into this and then, it fixes in 2 minutes.
Regrets from Mexico.
Do you know how to do a double break? Using this with the sharepoint https send of the following command adds an extra ” to the end that breaks the expression: “text”: “@{concat(variables(‘var_changesArray’),decodeUriComponent(‘%0A’),decodeUriComponent(‘%0A’))}”
}
Thanks!
Hello Mike,
try to put both the characters inside single decodeUriComponent(…)
– decodeUriComponent(‘%0A%0A’)
Lifesaver!!! I was trying to replace newline with backslash n but to no avail. This fixed my issue in no time! Legend!
I spend days playing around to split and replace line breaks. Thanks to you I finally made it, nothing other I found did work.
decodeUriComponent(‘%0A’)
Is the key! Thanks a lot!
Data migration imported description field that is concatinated with multiple source fields with a SQL Char(13) . Now trying to generate a CSV file – format breaks because of line break. Applied all suggestions above and did not help. Am I missing anything?
Hello Ronaldo,
it’s hard to tell if you’re missing something without seeing the actual data.
Thanks a lot! I had been stuck on this for a while.
I want to send and email from a cell in excel, the text from the cell has new line characters but when I put it in the “send email” action it delete the new lines. I tried to compose an expression as you showed but still when it’s inside the “send email” action it deletes the new lines and send the email in just one line.
Any ideas???
Hello Alex,
no ideas at this moment.
Excellent article!!! I was also stuck and ignorant about this Power Automation limitation. Thank you very much 🙂
This is great for one line break, but I tried for multiple and couldn’t get to work. Thoughts?
eg of String below. I want to leave the one break between Created comments, but remove the multiple line break to only one. I tried adding multiple here, but no luck.
decodeUriComponent(‘%0A%0A%0A%0A%0A’)
Created: 09-28-2023
SH no expire – this won’t be removed
nope
nada
Created: 09-28-2023
SH Expire – This will be removed after test
Created: 09-27-2023
SH No Expiration – This will not expire.
All good here.
This is how I want it formatted:
Created: 09-28-2023
SH no expire – this won’t be removed
nope
nada
Created: 09-28-2023
SH Expire – This will be removed after test
Created: 09-27-2023
SH No Expiration – This will not expire.
All good here.
OK…it removed the line breaks in my comment. There are 5 in-between the second and third comment. Want to remove to make only 1.