Forum Discussion

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

Bronze Contributor

Mar 13, 2024

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.

Peter Bartholomew

replied to g-00000

‎Mar 13 2024 06:08 PM

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

Copper Contributor

Mar 14, 2024

https://filetransfer.io/data-package/BFBuo9vI#link Thx for the link.

What I mean linebreak is the alt+enter linebreak, like
Aaaaaaaa
aaaaaaaa
aaaaaaaa
(Three lines because I clicked enter)
not wrap like
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
(Many “lines” because of being too long)

I’ve set wrap in the question’s file.

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
11.xlsx10 KB
- 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)
  
  112.xlsx10 KB
  - 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