Skip to main content
Question

Is there a way to replace carriage returns in a text box using a formula field?

  • May 3, 2017
  • 0 replies
  • 91 views
I would like to create a formula text field that replaces all carriage returns in another multi-line field with a comma or period or some other delimiter. Is there a way to do this?

Thanks in advance.

      Forum|alt.badge.img+3

      I have fought this issue many times and here is what I have found as I hope this helps others out!

      The issue is with non-printing characters & there are different ways of dealing with "new lines" as Windows, Mac & Unix/Linux variants can deal with this differently. 

      Some only add <carriage return>, <New Line> or Both of them at the end of each line. yet there are actually 3 possibilities:

      • Carriage return means to return to the beginning of the current line without advancing downward. The name comes from a printer's carriage, as monitors were rare when the name was coined. This is commonly escaped as "\r", abbreviated CR, and has ASCII value 13 or 0xD.
      • Linefeed means to advance downward to the next line; however, it has been repurposed and renamed. Used as "newline", it terminates lines (commonly confused with separating lines). This is commonly escaped as "\n", abbreviated LF or NL, and has ASCII value 10 or 0xA. CRLF (but not CRNL) is used for the pair "\r\n".
      • Form feed means advance downward to the next "page". It was commonly used as page separators, but now is also used as section separators. Text editors can use this character when you "insert a page break". This is commonly escaped as "\f", abbreviated FF, and has ASCII value 12 or 0xC.

       

      So you might be able to get away with a formula like this: (Replaces the "newline" with "|"
         SearchAndReplace(
            SearchAndReplace(
               SearchAndReplace( [Notes], "r\n", "|"),
            "r", "|"),
         "\n", "|")

      If that does not work, you have other non-printable characters at play so use this site to help you see what your text actually has:
      https://www.soscisurvey.de/tools/view-chars.php

      Then you would need to UUEncode the text to "see" all of the problem characters, then run a SearchandReplace() for EACH DIFFERENT non-printable character, then UUDecode it back to text.  It is not pretty but it works well to find and remove Non-printing ASCII, Unicode & emoji Characters which cause problems, if your users & upstream processes can add these characters.

      NOTEs:

      • Quickbase HTML processing will modify what is displays on the view form, to see what is actually in the field you must EDIT the record and get the data from the field.
      • There is some issue that I nor support have been able to identify, where unicode characters in a record will cause Connected tables to throw update errors with existing records. I have found that I have to delete the offending record in Quickbase to get the new data. Luckily is it not frequent that this happens for me.

       

      I hope this will point you to a solution.

      Terms & ConditionsAccessibility statement