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

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 .

Copper Contributor

Mar 13, 2024

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

Peter Bartholomew
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
- g-00000
  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

Resources