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

Copper Contributor

g00000_0-1710265644889.png

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), making each line be in a single cell, even if these linebreaks are manual linebreaks (^l) instead of paragragh marks (^p).

 

HOW TO DESTROY NOTHING?

19 Replies
This doesn't look like an appropriate use of Excel in the first place; why aren't you just doing the task (whatever it is) in Word? Is there something off screen that mandates Excel?

Yes, want to memorize vocabulary using excel, for its function suits this purpose better than other softwares.

Which "Excel function" suits the purpose of helping memorize vocabulary?
You've already said that Word works better in the Find and Replace function. Word also has its own Tables--i.e., rows and columns--for organizing data systematically. So what else is unique to Excel that you're referring to?

The excel’s suitable function is making the window 3x1, leaving one row and three columns shown. If recognizing the word, then press enter to go to the next row; if not, go to the fourth column to see the explanation, and type “1” in the third column, then go back to the left end, then go down to the next row. After finishing one turn, sort the third column to make rows with “1” be at the top, so they can be reviewed with priority. If still not recognized, change “1” to “2”, so sorting again makes rows with “2” be at the top, then rows with “1”, then words recognized.

press ctrl+shift+→ to see the explanation

why need press ctrl+shift+→ to see the explanation?
As far as I know press ctrl+shift+→ is to select current row.
Why do you need select the second column?
if not recognize the word,
it looks more reasonable is to press ctrl+→ move to select the third column cell and enter 1.


Word table do not good at filter by the priority.
maybe record a macro to handle it.

I remembered wrongly the key. I mean go to the fourth column, which cannot be seen initially in 3x1 window. Edited it.

=textjoin(char(10),,let(a,textsplit(j1,char(10)),if(right(a)=":","",a)))

This destroys both formatting and linebreak.

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 

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

@peiyezhu 

g00000_0-1710344692665.png

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

g00000_4-1710345342610.png

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

@Peter Bartholomew 

Yes, real text is:

 

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

 

(Only four lines)

@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

 

IMG_20240314_070412.jpg

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 

=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)

 

 

 

 

Screenshot_2024-03-14-14-38-03-327_com.microsoft.office.excel.jpg

 This is GRE800

@peiyezhu 

Oh yes indeed. Sorry didn’t change:to: in 1.xlsx.
But this still doesn’t fix the problem of destroying formatting.

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