大家好,又见面了,我是你们的朋友全栈君。
excel 中添加换行符:
:alt+enter
去掉excel中的换行符有三种方法:
注:解决过程中翻阅其他博客,看到如下方式:
1、看到有的说全选后“取消自动换行”,保存后,再打开,依然存在换行符
2、ctrl+H,然后按住alt输入“10”或者“0010”,然后替换,测试无效,可能我操作不对
解决办法:
M1:
M2 and M3:不翻译了,看起来有点麻烦
原文链接:https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/
Pros: you can use a formula chain / nested formulas for complex cell text processing. For example, it is possible to remove carriage returns and then eliminate excess leading and trailing spaces and those between words.
Or you may need to delete carriage returns to use your text as an argument of another function without changing the original cells. For example, if you want to be able to use the result as an argument of the function =lookup ().
Cons: you’ll need to create a helper column and follow many extra steps.
=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")
=TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")
=CLEAN(B2)
Pros: Being created once, can be reused in any workbook.
Cons: you need to have the basic knowledge of VBA.
The VBA macro from the example below deletes carriage returns from all cells in the currently opened worksheet (active worksheet).
Sub
RemoveCarriageReturns()
Dim
MyRange
As
Range
Application.ScreenUpdating =
False
Application.Calculation = xlCalculationManual
For
Each
MyRange
In
ActiveSheet.UsedRange
If
0 < InStr(MyRange, Chr(10))
Then
MyRange = Replace(MyRange, Chr(10),
""
)
End
If
Next
Application.ScreenUpdating =
True
Application.Calculation = xlCalculationAutomatic
End
Sub
If you don’t know VBA really well, see How to insert and run VBA code in Excel
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/167854.html原文链接:https://javaforall.cn