Forum Discussion

g-00000's avatar
g-00000
Copper Contributor
Mar 12, 2024

How to replace without destroying formatting and in-cell-linebreak?

I want what in the red circle. Using replace in excel destroys formating. Using replace in word doesn't destroy formating, but pasting back to excel destroys linebreaks in one cell (alt+enter...
excel
wdx223_Daniel's avatar
wdx223_Daniel
Brass Contributor
Mar 13, 2024
=textjoin(char(10),,let(a,textsplit(j1,char(10)),if(right(a)="：","",a)))
g-00000's avatar
g-00000
Copper Contributor
Mar 13, 2024

This destroys both formatting and linebreak.

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Mar 13, 2024

    Have you tested the formula?
    I recommend you upload a test file.
    As far as I know the formular provided by wdx223_Daniel keep line break at least.
    Other format, i.g. bold font or blue color font may disappear.

    If you want precise the format,I guess you need parse and formatting .

    • g-00000's avatar
      g-00000
      Copper Contributor
      Mar 13, 2024

      peiyezhu 

      Want to upload file, but this website says "The file type (.xlsx) is not supported". I'll describe by text:

      Line breaks are alt+enter.

      In the question's file, line breaks were made through
      =D1&CHAR(10)&"例："&E1&CHAR(10)&"近："&F1&CHAR(10)&"派："&G1

      After combining the previous cells together, I added formatting manually.

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Mar 13, 2024

        g-00000 

         

        https://filetransfer.io/

        upload file here and share the link.

         

        When you apply char(10) & "例子",you should set cell wrap text as well.

         

         

        Peter has mentioned it before.

         

         replied to  g-00000
         

        ‎Mar 13 2024 06:08 PM

         

        @g-00000 

        Are you sure that the cells in question have 'Wrap Text' selected in each case

         

         

Resources