excel如何去掉换行符号_去掉文本中所有换行符

全栈程序员-站长 • 2022年8月4日下午4:46 • 未分类 • 阅读 65

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元售后保障童叟无欺

excel 中添加换行符：

　　：alt+enter

去掉excel中的换行符有三种方法：

注：解决过程中翻阅其他博客，看到如下方式：

1、看到有的说全选后“取消自动换行”，保存后，再打开，依然存在换行符

2、ctrl+H，然后按住alt输入“10”或者“0010”，然后替换，测试无效，可能我操作不对

解决办法：

M1：

直接查找替换，选中你要替换的位置or全选（ctrl+a）
然后按 ctrl+h，打开替换界面
在替换内容窗口，输入ctrl+j，看起来是空的，但是你能看到一个点闪烁。
在替换为窗口，输入你要替换的内容，什么也不输入表示删掉
然后按照需要选择全部替换，或者替换

M2 and M3：不翻译了，看起来有点麻烦

原文链接：https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/

Delete line breaks using Excel formulas

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.

Add the helper column to the end of your data. You can name it “1 line”.
In the first cell of the helper column (C2), enter the formula to remove / replace line breaks. Here you can see several helpful formulas for different occasions:
- Handle both Windows and UNIX carriage return/ line feeds combinations.
  =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")
- The next formula will help you replace line break with any other symbol (comma+space). In this case lines will not join and extra spaces will not appear.
  =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")
- If you want to remove all nonprintable characters from text, including line breaks:
  =CLEAN(B2)
Copy the formula across the other cells in the column.
Optionally, you can replace the original column with the one where the line breaks were removed:
- Select all cells in column C and press Ctrl + C to copy the data to clipboard.
- Now pick the cell B2 and press the Shift + F10 shortcut. Then just press V.
- Remove the helper column.

VBA macro to get rid of line breaks

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