Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Load Analysis ToolPak in Excel
  • How To Insert and Customize Sparklines in Excel
  • How To Filter Data in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How to calculate correlation coefficient Correlation in Excel

References

  • Perform case-sensitive Lookup in Excel
  • Find closest match in Excel
  • To count total rows in a range in Excel
  • How to get last column number in range in Excel
  • Count unique text values with criteria

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation unique values only
  • Excel Data validation whole percentage only
  • Excel Data validation require unique number
  • Excel Data validation must begin with

Remove line breaks in a cell in Excel

by

To remove line breaks from a cell, or from text inside a formula, you can use a formula based on the SUBSTITUTE and CHAR functions.

Formula

=SUBSTITUTE(A1,CHAR(10),", ")

Explanation

In the example shown, the formula in C5 is:

=SUBSTITUTE(B5,CHAR(10),", ")

which replaces line breaks in B5 with commas.

How this formula works

First, you should know that Excel contains two functions, CLEAN and TRIM, that can automatically remove line breaks and extra spaces from text. For example to strip all line breaks from a cell, you could use:

=CLEAN(B5)

In this case, however, we are removing line breaks and replacing them with commas, so we are using the SUBSTITUTE function instead of CLEAN. SUBSTITUTE can locate matching text anywhere in a cell, and replace it with the text of your choice. SUBSTITUTE can accept up to four arguments, but we are using only the first three like this:

=SUBSTITUTE(B5,CHAR(10),", ")

The text comes from cell B5.

The “old text” is entered as CHAR(10). This will match the line break character used in Windows.

The “new text” is entered as “, “. This translates to a comma plus one space. We need the quotes because this is a text value.

SUBSTITUTE then replaces all line breaks in the cell with commas and returns the final result as text in C5. Because “old text” is an argument, you can change the comma to any other text you like.

Post navigation

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

Next Post:

Create One-dimensional and Two-dimensional Array

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Extract multiple matches into separate rows in Excel
  • Invoice status with nested if in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel TRUE Function
  • How to use Excel OR Function

Date Time

  • Convert decimal hours to Excel time
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Count birthdays by month in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Dynamic date list in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel
  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel
  • Group arbitrary text values in Excel

General

  • How to make excel worksheets print on one page?
  • How to count total number of cells in a rectangular range in Excel
  • How to calculate project complete percentage in Excel
  • Mark Workbook as Final in Excel
  • Subtotal by color in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning