How to Find Carriage Return and Line Feed Characters in WPS Office Spreadsheets
Question details
The user needs to locate ASCII control characters, specifically line feed and carriage return characters, inside spreadsheet text using the CHAR and FIND functions.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with multi-line text in a WPS Office spreadsheet and needs to find the exact position of line break characters for cleanup, parsing, or analysis.
- Observed behavior
- Line break characters are invisible in the cell, and formulas fail when CHAR(10) or CHAR(13) is enclosed in quotation marks.
Open the workbook in WPS Office Spreadsheets and identify the cell containing the multi-line text. Make sure you enter the formula in an empty cell so the returned position number is not overwritten.
Find the first line feed or carriage return with CHAR and FIND
Use this solution when you need to locate the first line break character in a specific cell and see its numeric position.
Excel-compatible functions CHAR and FIND work the same way in WPS Office Spreadsheets. CHAR(10) returns a line feed, CHAR(13) returns a carriage return, and FIND returns the starting position of that character.
Click an empty cell where you want the character position to appear.
Type =FIND(CHAR(10),$C$13) and press Enter to find the first line feed in cell C13.
To find a carriage return instead, use =FIND(CHAR(13),$C$13) and press Enter.
Do not place quotation marks around CHAR(10) or CHAR(13); they are functions, not text strings.
Find later occurrences by nesting FIND with a start position
Use this solution when a cell contains multiple line breaks and you need to find the second, third, or later character position.
Locate line break characters directly in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel's CHAR and FIND functions, so you can use =FIND(CHAR(10),C13) or =FIND(CHAR(13),C13) to pinpoint line feed and carriage return characters in your existing Excel-compatible workbooks.
- 1. Download and install WPS Office: Download WPS Office from the official WPS website and install it on your device.
- 2. Open the Excel-compatible workbook: Launch WPS Office and open your .xlsx, .xls, or .csv file in WPS Office Spreadsheets.
- 3. Enter the line feed formula: Click an empty cell and type =FIND(CHAR(10),cell_reference) to find a line feed.
- 4. Enter the carriage return formula: For a carriage return, use =FIND(CHAR(13),cell_reference) in another empty cell.
- 5. Confirm the result: Press Enter to see the numeric position of the control character in the cell.
Frequently Asked Questions
Why does =FIND("CHAR(10)",C13) return #VALUE!?
Because the quotation marks make CHAR(10) a text string instead of the line feed character. Remove the quotes and use =FIND(CHAR(10),C13).
How do I find the second or third line break in a cell?
Use the previous result plus 1 as the start position. For example, if D13 contains the first position, use =FIND(CHAR(10),C13,D13+1) to find the next occurrence.
What is the difference between CHAR(10) and CHAR(13)?
CHAR(10) returns a line feed character, while CHAR(13) returns a carriage return character. Windows line breaks often use both together as CRLF.
Can I use these formulas in both WPS Office and Microsoft Excel?
Yes, WPS Office Spreadsheets supports the same FIND and CHAR functions, so these formulas work in Excel-compatible .xlsx and .xls files.
How can I remove carriage returns or line feeds after finding them?
Use a formula such as =SUBSTITUTE(C13,CHAR(13),"") to remove carriage returns, or =CLEAN(C13) to remove many non-printable characters from a cell.
