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

Data Analysis

  • How To Create Frequency Distribution in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Create Scatter Chart in Excel
  • How to create Checklist in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS

References

  • How to get address of first cell in range in Excel
  • Extract data with helper column in Excel
  • Vlookup Examples in Excel
  • Extract all partial matches in Excel
  • Find closest match in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation don’t exceed total
  • Excel Data validation date in next 30 days
  • Excel Data validation with conditional list
  • Data validation must not exist in list

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.

Worked Example:   How to extract last two words from text string in Excel

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

Worked Example:   Convert column number to letter in Excel

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

Worked Example:   Split dimensions into two parts in Excel Worksheet

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

  • How to use Excel TRUE Function
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel OR Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function

Date Time

  • Excel Date & Time Functions Example
  • Get month name from date in Excel
  • Get week number from date in Excel
  • Add decimal hours to time in Excel
  • Add decimal minutes to time in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • Running count group by n size in Excel
  • Calculate conditional mode with criteria in Excel
  • Categorize text with keywords in Excel
  • If cell contains one of many things in Excel

General

  • How to count total columns in range in Excel
  • How to Insert Cells, Row and Rows in Excel
  • Common Errors in Excel
  • Count cells less than in Excel
  • How to calculate percent of students absent in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning