Return symbol - Revision history

Planetoid: /* 如何尋找與取代換行符號 */

2025-11-28T08:32:06Z

如何尋找與取代換行符號

← Older revision		Revision as of 16:32, 28 November 2025
Line 153:		Line 153:
	</pre>		</pre>

	=== Excel 如何尋找換行符號 ===		=== Excel 或 Google Sheet 如何尋找換行符號 ===
	判斷是哪種換行符號：		判斷是哪種換行符號：
	<pre>		<pre>
Line 162:		Line 162:
	</pre>		</pre>

	=== Excel 如何取代換行符號 ===		=== Excel 或 Google Sheet 如何取代換行符號 ===
	For Microsoft Excel or Google Sheet		For Microsoft Excel or Google Sheet
	* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.		* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.
Line 172:		Line 172:
	* {{kbd \| key=CHAR(10)}} means "Line feed" (LF, {{kbd \| key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號		* {{kbd \| key=CHAR(10)}} means "Line feed" (LF, {{kbd \| key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號

	~~=== Google Sheet 如何取代換行符號 ===~~
	For Google Sheet		For Google Sheet
	* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} replaces return symbols with spaces in cell {{kbd \| key=A1}} in Google Sheets		* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} replaces return symbols with spaces in cell {{kbd \| key=A1}} in Google Sheets

Planetoid: /* Excel 如何取代換行符號 */

2025-11-28T08:31:17Z

Excel 如何取代換行符號

← Older revision		Revision as of 16:31, 28 November 2025
Line 172:		Line 172:
	* {{kbd \| key=CHAR(10)}} means "Line feed" (LF, {{kbd \| key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號		* {{kbd \| key=CHAR(10)}} means "Line feed" (LF, {{kbd \| key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號

			=== Google Sheet 如何取代換行符號 ===
	For Google Sheet		For Google Sheet
	* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} replaces return symbols with spaces in cell {{kbd \| key=A1}} in Google Sheets		* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} replaces return symbols with spaces in cell {{kbd \| key=A1}} in Google Sheets

Planetoid: /* Excel 如何取代換行符號 */

2025-11-04T10:16:41Z

Excel 如何取代換行符號

← Older revision		Revision as of 18:16, 4 November 2025
Line 163:		Line 163:

	=== Excel 如何取代換行符號 ===		=== Excel 如何取代換行符號 ===
			For Microsoft Excel or Google Sheet
	* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.		* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.
	* {{kbd \| key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} replace the return symbols with spaces in the cell located at {{kbd \| key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access \| date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>. ~~for Microsoft Excel or Google Sheet~~		* {{kbd \| key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} replace the return symbols with spaces in the cell located at {{kbd \| key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access \| date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>.
	* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} replaces return symbols with spaces in cell {{kbd \| key=A1}} in Google Sheets

	函數說明		函數說明
Line 171:		Line 171:
	* {{kbd \| key=CHAR(13)}} means "Carriage return"(CR, {{kbd \| key=<nowiki>\r</nowiki>}}) 舊版 Mac 換行符號		* {{kbd \| key=CHAR(13)}} means "Carriage return"(CR, {{kbd \| key=<nowiki>\r</nowiki>}}) 舊版 Mac 換行符號
	* {{kbd \| key=CHAR(10)}} means "Line feed" (LF, {{kbd \| key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號		* {{kbd \| key=CHAR(10)}} means "Line feed" (LF, {{kbd \| key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號

			For Google Sheet
			* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} replaces return symbols with spaces in cell {{kbd \| key=A1}} in Google Sheets

			函數說明
			* {{kbd \| key=<nowiki>[\r\n]</nowiki>}} 符合任意類型的換行符號
			* {{kbd \| key=<nowiki>[\r\n]+</nowiki>}} 符合任意類型的換行符號，出現一次或連續出現

	=== Python 如何取代換行符號 ===		=== Python 如何取代換行符號 ===

Planetoid: /* Excel 如何尋找換行符號 */

2025-11-04T10:13:44Z

Excel 如何尋找換行符號

← Older revision		Revision as of 18:13, 4 November 2025
Line 154:		Line 154:

	=== Excel 如何尋找換行符號 ===		=== Excel 如何尋找換行符號 ===
	~~'''~~判斷是哪種換行符號：~~'''~~		判斷是哪種換行符號：
	<pre>		<pre>
	=IF(ISNUMBER(SEARCH(CHAR(13)&CHAR(10), A1)), "CRLF (\r\n)",		=IF(ISNUMBER(SEARCH(CHAR(13)&CHAR(10), A1)), "CRLF (\r\n)",
Line 161:		Line 161:
	"無換行符號")))		"無換行符號")))
	</pre>		</pre>


	=== Excel 如何取代換行符號 ===		=== Excel 如何取代換行符號 ===

Planetoid: /* Excel 如何取代換行符號 */

2025-11-04T10:13:28Z

Excel 如何取代換行符號

@@ Line 152: / Line 152: @@
 </pre>
 === Excel 如何取代換行符號 ===
 * {{kbd | key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.
 * {{kbd | key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} replace the return symbols with spaces  in the cell located at {{kbd | key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access | date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>. for Microsoft Excel or Google Sheet
@@ Line 163: / Line 172: @@
 * {{kbd | key=CHAR(13)}} means "Carriage return"(CR, {{kbd | key=<nowiki>\r</nowiki>}}) 舊版 Mac 換行符號
 * {{kbd | key=CHAR(10)}} means "Line feed" (LF, {{kbd | key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號
 === Python 如何取代換行符號 ===

Planetoid: /* Excel 如何取代換行符號 */

2025-11-04T10:12:35Z

Excel 如何取代換行符號

← Older revision		Revision as of 18:12, 4 November 2025
Line 156:		Line 156:
	'''取代換行符號'''		'''取代換行符號'''
	* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.		* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.
	* {{kbd \| key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} to replace the return ~~symbol~~ in the cell located at {{kbd \| key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access \| date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>. for Microsoft Excel or Google Sheet		* {{kbd \| key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} replace the return symbols with spaces in the cell located at {{kbd \| key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access \| date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>. for Microsoft Excel or Google Sheet
	* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} ~~to replace the~~ return ~~symbol~~ in ~~the~~ cell ~~located at~~ {{kbd \| key=A1}} ~~for~~ Google ~~Sheet~~		* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} replaces return symbols with spaces in cell {{kbd \| key=A1}} in Google Sheets

	函數說明		函數說明

Planetoid: /* Excel 如何取代換行符號 */

2025-11-04T10:10:53Z

Excel 如何取代換行符號

← Older revision		Revision as of 18:10, 4 November 2025
Line 157:		Line 157:
	* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.		* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.
	* {{kbd \| key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} to replace the return symbol in the cell located at {{kbd \| key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access \| date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>. for Microsoft Excel or Google Sheet		* {{kbd \| key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} to replace the return symbol in the cell located at {{kbd \| key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access \| date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>. for Microsoft Excel or Google Sheet
	* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} for Google Sheet		* {{kbd \| key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} to replace the return symbol in the cell located at {{kbd \| key=A1}} for Google Sheet

	函數說明		函數說明

Planetoid at 10:10, 4 November 2025

2025-11-04T10:10:18Z

← Older revision		Revision as of 18:10, 4 November 2025
Line 189:		Line 189:
	<references />		<references />

	[[Category:Programming]] [[Category:Data Science]] [[Category:String manipulation]]		[[Category: Programming]]
			[[Category: Data Science]]
			[[Category: String manipulation]]
			[[Category: Revised with LLMs]]

Planetoid: /* Excel 如何取代換行符號 */

2025-11-04T10:08:51Z

Excel 如何取代換行符號

← Older revision		Revision as of 18:08, 4 November 2025
Line 160:		Line 160:

	函數說明		函數說明
			* {{kbd \| key=CONCAT(CHAR(13), CHAR(10))}} means "CRLF"({{kbd \| key=<nowiki>\r\n</nowiki>}}) Windows 標準換行符號
			* {{kbd \| key=CHAR(13)}} means "Carriage return"(CR, {{kbd \| key=<nowiki>\r</nowiki>}}) 舊版 Mac 換行符號
	* {{kbd \| key=CHAR(10)}} means "Line feed" (LF, {{kbd \| key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號		* {{kbd \| key=CHAR(10)}} means "Line feed" (LF, {{kbd \| key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號
	* {{kbd \| key=CHAR(13)}} means "Carriage return"(LF, {{kbd \| key=<nowiki>\r</nowiki>}}) 舊版 Mac 換行符號
	* {{kbd \| key=CONCAT(CHAR(13), CHAR(10))}} means "CRLF"({{kbd \| key=<nowiki>\r\n</nowiki>}}) Windows 標準換行符號

	'''判斷是哪種換行符號：'''		'''判斷是哪種換行符號：'''

Planetoid: /* Excel 如何取代換行符號 */

2025-11-04T10:07:49Z

Excel 如何取代換行符號

← Older revision		Revision as of 18:07, 4 November 2025
Line 154:		Line 154:

	=== Excel 如何取代換行符號 ===		=== Excel 如何取代換行符號 ===
			'''取代換行符號'''
	* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.		* {{kbd \| key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.
	* {{kbd \| key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} to replace the return symbol in the cell located at {{kbd \| key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access \| date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>. for Microsoft Excel or Google Sheet		* {{kbd \| key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} to replace the return symbol in the cell located at {{kbd \| key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access \| date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>. for Microsoft Excel or Google Sheet
Line 163:		Line 164:
	* {{kbd \| key=CONCAT(CHAR(13), CHAR(10))}} means "CRLF"({{kbd \| key=<nowiki>\r\n</nowiki>}}) Windows 標準換行符號		* {{kbd \| key=CONCAT(CHAR(13), CHAR(10))}} means "CRLF"({{kbd \| key=<nowiki>\r\n</nowiki>}}) Windows 標準換行符號

	判斷是哪種換行符號：		'''判斷是哪種換行符號：'''
	<pre>		<pre>
	=IF(ISNUMBER(SEARCH(CHAR(13)&CHAR(10), A1)), "CRLF (\r\n)",		=IF(ISNUMBER(SEARCH(CHAR(13)&CHAR(10), A1)), "CRLF (\r\n)",