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
peiyezhu's avatar
peiyezhu
Bronze Contributor
Mar 14, 2024

g-00000 

Hi,

 

Please check the attachment.

 

Warp Text

 

https://support.microsoft.com/zh-cn/office/%E5%9C%A8%E5%8D%95%E5%85%83%E6%A0%BC%E4%B8%AD%E8%87%AA%E5%8A%A8%E6%8D%A2%E8%A1%8C-2a18cff5-ccc1-4bce-95e4-f0d4f3ff4e84#:~:text=%E6%96%87%E6%9C%AC%E8%87%AA%E5%8A%A8%E6%8D%A2%E8%A1%8C%201%20%E5%9C%A8%E5%B7%A5%E4%BD%9C%E8%A1%A8%E4%B8%AD%EF%BC%8C%E9%80%89%E6%8B%A9%E8%A6%81%E8%AE%BE%E7%BD%AE%E6%A0%BC%E5%BC%8F%E7%9A%84%E5%8D%95%E5%85%83%E6%A0%BC%E3%80%82%202%20%E5%9C%A8%E2%80%9C%20%E5%BC%80%E5%A7%8B%20%E2%80%9D%E9%80%89%E9%A1%B9%E5%8D%A1%E4%B8%8A%E7%9A%84%E2%80%9C%20%E5%AF%B9%E9%BD%90%E6%96%B9%E5%BC%8F,%2B%20H%20%2B%20W%20.%29%20%E6%B3%A8%E6%84%8F%3A%20%E5%8D%95%E5%85%83%E6%A0%BC%E4%B8%AD%E7%9A%84%E6%95%B0%E6%8D%AE%E8%87%AA%E5%8A%A8%E6%8D%A2%E8%A1%8C%E4%BB%A5%E9%80%82%E5%BA%94%E5%88%97%E5%AE%BD%EF%BC%8C%E5%BD%93%E6%9B%B4%E6%94%B9%E5%88%97%E5%AE%BD%E6%97%B6%EF%BC%8C%E6%95%B0%E6%8D%AE%E6%8D%A2%E8%A1%8C%E4%BC%9A%E8%87%AA%E5%8A%A8%E8%B0%83%E6%95%B4%E3%80%82%20%E5%A6%82%E6%9E%9C%E6%89%80%E6%9C%89%E6%8D%A2%E8%A1%8C%E6%96%87%E6%9C%AC%E4%B8%8D%E5%8F%AF%E8%A7%81%EF%BC%8C%E5%88%99%E5%8F%AF%E8%83%BD%E6%98%AF%E5%9B%A0%E4%B8%BA%E8%AF%A5%E8%A1%8C%E8%A2%AB%E8%AE%BE%E7%BD%AE%E4%B8%BA%E7%89%B9%E5%AE%9A%E7%9A%84%E9%AB%98%E5%BA%A6%E6%88%96%E5%9C%A8%E6%9F%90%E5%8D%95%E5%85%83%E6%A0%BC%E5%8C%BA%E5%9F%9F%E5%86%85%E7%9A%84%E6%96%87%E6%9C%AC%E5%B7%B2%E5%90%88%E5%B9%B6%E3%80%82

peiyezhu's avatar
peiyezhu
Bronze Contributor
Mar 14, 2024

peiyezhu 

=TEXTJOIN(CHAR(10),,LET(a,TEXTSPLIT(A1,CHAR(10)),IF(RIGHT(a)=":","",a)))

 

instead of

=TEXTJOIN(CHAR(10),,LET(a,TEXTSPLIT(A2,CHAR(10)),IF(RIGHT(a)="：","",a)))

 

: not ：(Chinese)

 

 

 

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

    g-00000 

    If you want to keep bold and blue font while clean some characters,it looks some difficulty to achieve.
    Because you can see just a single replace need so long function.If you want to parse bold and blue fonts and then reorgnize and render them,I guess not an easy work.

     

    Btw:

    "n.意外发现珍奇事物的能力 luck, or good fortune, in finding something good accidentally 例：result of serendipity 机缘巧合的结果 近： 派："

     

     

    Why luck and accidentally are bold?

     

    Can tell them or pick out them by some hints?

    Color of serendipity  is blue because this is the vocabulary itself.

     

     

  • g-00000's avatar
    g-00000
    Copper Contributor
    Mar 15, 2024
    Oh yes indeed. Sorry didn’t change：to: in 1.xlsx.
    But this still doesn’t fix the problem of destroying formatting.

Resources