Forum Discussion

AymanHanash's avatar
AymanHanash
Qrew Trainee
4 years ago

How To remove carriage return from Multi-Line text field

Hello,

I have searched for this subject and found 1 post with 0 responses.  

I'm pulling in info from QB using Power Query, a Multi-line text field is not pulling at all when there

are multiple lines created using a carriage return / line feed.  Is there a function similar to CLEAN (instead of TRIM)? or how can I replace the non visible carriage return using another text formula field (without taking the table down clean it and re-uploading)

thanks,



------------------------------
Ayman Hanash
------------------------------
Formulas and functions

8 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    Try this as a formula text field.

    SearchAndReplace([My multi line text field], "\n", " ")

    That says to replace any carriage returns with a space.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • AymanHanash's avatar
      AymanHanash
      Qrew Trainee
      4 years ago
      Mark, Thanks,  "\n" did the trick....

      ------------------------------
      Ayman Hanash
      ------------------------------
    • AymanHanash's avatar
      AymanHanash
      Qrew Trainee
      4 years ago

      Hey Mark,

      the "\n" helped with some lines, but other lines power query didn't pickup, are there any other invisible 

      characters aside from the carriage return?  



      ------------------------------
      Ayman Hanash
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        You might try to copy and paste the result into some kind of notepad type app (not Word) to try to see what is really there.

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------
  • RicardoSendra__'s avatar
    RicardoSendra__
    Qrew Cadet
    21 days ago

    The issue is with other non-printing characters.
    Windows, Mac & Windows deals with this differently. 
    Some only add <carriage return>, <New Line> or Both of them at the end of each line.
    "\n" will only get <carriage return>
    You can UUEncode the text to get all of the problem characters, then run a SearchandReplace() for each one then decode the text.  it is not pretty but it works.
    This also work to find and remove Non-printing ASCII, Unicode & emoji Characters which can cause problems.

    NOTE: 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.

    I hope this will point you to the correct information.