How to Add a Line Break and Clean Strange Characters within a Excel Formula


In this tutorial,  you will learn how to add a line break and clean strange characters in Excel.

Add Line Break

When creating charts in Excel, it’s sometimes useful to force line breaks for the purpose of better visualization.

The X-axis labels in the chart shown here, for example,  include the data value in addition to the sales rep. This setup works well when you don’t want to inundate your chart with data labels.

Line Break

The secret to this trick is to use CHAR() function in a formula that makes up your chart labels, it forces a line break between sales rep name and data value.

The code for a line break in CHAR() function is 10, and 32 is for a space. The cell itself doesn’t show the line break unless you have wrap text applied.

Clean Strange Characters

Strange characters may come in with your data in Excel when you import data from an external data source. Instead of trying to clean these manually, you can use Excel’s CLEAN() function to format the data.

The CLEAN function removes non-printable characters from any text string. you can wrap the CLEAN function with a TRIM function together to remove unprintable characters and excess spaces at the same time.


Comments are closed.