Detect CRLF character in a text line item

CommunityMember126322

I have to replace CRLF (carriage return / line feed) characteres form a text line item.

How can I use the FIND function or more better the SUBSTITUE function to replace such a CRLF char.

Result = SUBSTITUE(line item to replace, CRLF character, " ")

Accepted answers

CommunityMember126322

Bingo - yes this will solve my problem directly 😀

All comments

Alexandru.Rosca

Hi, could you please be more specific? What is not working if using substitute function? That should suit the purpose.

CommunityMember126322

Thanks for your answers, but I want to replace the CRLF character from the text field with a space.

See below in T3 the text is abc abc, where the blank sign between first abc and 2nd abc is the CRLF caharacter.

now with SUBSTITUE function I try to replace the CRLF sign with a blank/space

The CR in the formula should be the CRLF character to replace.

The final result must then be "abc abc"

and not

"abc

abc"

Alexandru.Rosca

Pardon my ignorance, I see now what you mean by CR LF. Have you tried using trim? This will remove any irregular spaces

CommunityMember126322

I found a workaround:

In "Text with CRLF" I added this text:

In the line item "CRLF" I get the CRLF character with the MID function, which then I can use in the SUBSTITUTE function to replace the CRLF in any other text formatted line item (here in "Text to replace")

The question remains: Is there not an easier way to have the CRLF character as a short cut to use in SUBSTITUE without my workaround?

CommunityMember126322

Bingo - yes this will solve my problem directly 😀

Quick Links

Can you help?

Unanswered questions

Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…