Excel VBA: Remove Carriage Returns and Line Feeds from Text
Question details
The user needs to remove carriage return and line feed characters from text extracted from a PDF using Excel VBA.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- Text copied or extracted from a PDF contains unwanted line breaks, and the user wants to clean the value before using it elsewhere.
- Observed behavior
- TRIM removes extra spaces but does not remove the carriage return and line feed characters embedded in the text.
Confirm that sourceValue contains the extracted text and that textValue is the variable you want to clean. In VBA, carriage return is Chr(13) and line feed is Chr(10), so both characters must be removed.
Remove carriage returns and line feeds with VBA Replace
Use this method when PDF-extracted text contains hidden line-break characters that TRIM leaves behind.
In Excel, press Alt+F11, choose Insert > Module, and open a standard VBA module.
Assign the source text to the target variable with: textValue = Trim(sourceValue)
Add: textValue = Replace(textValue, Chr(13), "") to remove character 13 from the same text variable.
Add: textValue = Replace(textValue, Chr(10), "") to remove character 10 and finish cleaning the text.
Pass textValue to the next operation, such as writing it to a cell: Range("A1").Value = textValue.
Use a nested Replace expression
Use this compact version when you want to clean the value in a single VBA statement.
Clean PDF-Extracted Text with VBA in WPS Spreadsheets
Use WPS Spreadsheets to open compatible Excel workbooks, edit the VBA procedure when macro support is available, and apply Replace with Chr(13) and Chr(10) to remove hidden line breaks from extracted text.
- 1. Open the workbook in WPS: Launch WPS Office, select Spreadsheets, and open the XLSM or compatible Excel workbook containing the macro.
- 2. Open the macro editor: Use the Tools or Developer menu to open the VBA or macro editor, depending on your WPS version and macro support.
- 3. Add the cleanup code: In a standard module, assign Trim(sourceValue) to textValue, then use Replace(textValue, Chr(13), "") and Replace(textValue, Chr(10), "").
- 4. Run and verify the macro: Run the procedure from the macro editor and inspect the destination cell to confirm that the unwanted line breaks are gone.
Frequently Asked Questions
Why does Excel TRIM not remove line breaks from PDF text?
TRIM removes extra spaces and compresses repeated spaces, but it does not remove carriage return character 13 or line feed character 10. Use Replace with Chr(13) and Chr(10).
What VBA characters represent a carriage return and a line feed?
Chr(13) represents a carriage return, while Chr(10) represents a line feed. PDF-extracted text may contain either or both characters.
Can I remove both characters in one line of VBA?
Yes. Use textValue = Replace(Replace(Trim(sourceValue), Chr(13), ""), Chr(10), "") to trim the value and remove both line-break characters.
Why does my Replace statement leave the original text unchanged?
Replace returns a cleaned string; it does not modify the variable automatically. Assign the result back to the same variable, such as textValue = Replace(textValue, Chr(13), "").
Will this code remove visible spaces as well as line breaks?
The Trim function removes leading and trailing spaces, while the two Replace calls remove carriage returns and line feeds. Internal spaces remain unchanged.
