Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn about how to remove line breaks in Excel using FILTERXML function
Video
Sample Data
We have in the caption below, a sales record showing line break
Our goal is to write a long formula using the FILTERXML and other functions to remove the line breaks dynamically!
FILTERXML Function
The FILTERXML function returns specific data from XML content by using the specified xpath.
FILTERXML(xml, xpath)
The FILTERXML function syntax has the following arguments.
XML – a string in a valid XML Format
XPATH – a string in standard XPATH format
- To remove the line breaks using worksheet function, in cell A2 of the Solution Sheet, execute the formula
=IFERROR(FILTERXML(“<tom><jerry>”&SUBSTITUTE(TEXTJOIN(CHAR(10),TRUE,Data!A2:A3),CHAR(10),”</jerry><jerry>”)&”</jerry></tom>”,”//jerry”),””)
- Drag the formula across to the right. Watch the video for more in-depth explanation
In the caption below, we have the line breaks removed and the data is now in the Excel Way
See you in the next video!