在Excel 裡利用公式刪除前面或後面的字元

利用Excel 的LEFF 公式和RIGHT 公式，你可以將 字串裡前面和後面特定數量的字元刪除。請按照下面步驟操作：

從字串裡刪除前4個字元

1: 在相鄰儲存格B1裡輸入 =RIGHT(A1, LEN(A1)-4)， 點擊Enter 鍵，並將儲存格填充柄拖到需要應用此公式的儲存格中。現在你已成功地刪除了字串裡的前4個字元。如圖：

提示：這個公式將會從右邊返回原來的字元，你需要從字串的左邊減去4個字元。通過修改公式 =RIGHT(A1, LEN(A1)-4) 裡的數字4，你可以 指定從左邊字串裡刪除的字元數。

如果你需要刪除最後幾個字元，你可以按照使用RIGHT公式一樣的方法應用LEFT 公式。

資料來源:https://www.extendoffice.com/documents/excel/zh-tw-excel/2818-excel-remove-character-from-string.html

刪除多餘空白+換行

[Office] 刪除多餘空白+刪除換行符號

事由：

建立SQLite檔案時發現要貼入的文字檔有大量的空白與換行符號，導致貼入文字後的版面非常之難看。

解法：

1.刪除多餘空白： 

選擇「編輯」「取代」，或按 CTRL + H ，在「尋找目標欄位」輸入「（空白）」。
（就是在裡面按一次空白鍵，不是輸入"空白"），然後按「全部取代」。然後字與字之間的空白就被刪除掉了。

2.刪除換行符號： 

在 Word 裡面有兩種換行符號，一種是標準的換行（在行與行中間，會插入一行空白），
另外一種就是強制換行（效果就像文字到頁面邊緣自動換行）。

先刪除標準換行：選擇「編輯」「取代」，或按 CTRL + H ，在「尋找目標欄位」輸入「^p」，按「全部取代」。

再來刪除強制換行：
選擇「編輯」「取代」，或按 CTRL + H ，在「尋找目標欄位」輸入「^l」，按「全部取代」。

資料來源：

ALVIN部落格_流浪是為了找回自己

notepad++ 去掉换行

1、按ctrl+f

2、进替换/replace，

3、选中下方的“扩展”

4、“查找目标”输入：“\r\n”，“替换为”留空白

5、最后全部替换。

資料來源：https://zhidao.baidu.com/question/342881025.html

Excel (IF else)

If else           https://exceljet.net/formula/if-else

Generic formula 

=IF(test, true result, false result)

Explanation 

If you need to test a condition, then take one action if the condition is TRUE, and another action if the condition if FALSE, you can use the IF function.

Context

In the example shown, we have a list of T-shirts that includes color and size. However, the size is abbreviated as "S" for small and "L" for large. Lets say you want to write a formula to expand these abbreviations and show either the word "Small" or "Large" in column E. In other words:

1. If a cell in column D contains "S", return "Small".
2. If a cell in column D contains "L", return "Large".

This is a perfect application of the IF function.

Solution

The IF function is organized like this:

=IF(test, true result, false result)

In the example, we need to test cells in column D to see what they contain, so the formula we are using in cell E5 is:

=IF(D5="S","Small","Large")

Translated, this says:

IF cell D5 equals (contains) the letter "S", return the word "Small", ELSE return the word "Large".

Notice we are only testing for "S" — we don't need to test for "L". That's because we only have two possible values, and the ELSE part of the formula (the FALSE result) logically takes care of "L" for us: if the cell doesn't contain "S", it must be "L".

Nesting IFs to handle more conditions

This works fine for two conditions, but what if we have a third condition?, for example, "M" for "Medium"?

In that case, we need to extend the formula with another IF statement. We put the second IF statement, replacing the false result.

In the example below, we've added another size (Medium). The formula we are using in E5 is:

=IF(D5="S","Small",IF(D5="M","Medium","Large"))

This technique is called "nesting", since we are placing on function inside another. With it is used with the IF function, you'll sometimes hear it called a "Nested IF statement".