Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Searching for Line Breaks.
Veronica wondered how to search for a line break (Alt+Enter) in a cell. In Word you can search for ^l to find line breaks, but there does not seem to be a similar way to search for line breaks in Excel.
The answer is to remember that you can enter any ASCII code into the "Find What" box by holding down the Alt key and using the numeric keypad. Since the ASCII code for the line break is 10, you can follow these steps:
Figure 1. The Find tab of the Find and Replace dialog box.
If you want to find cells containing a line break through a macro, you can use the following:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Sub FindLineBreak()
WhatToFind = Chr(10)
Cells.Select
Selection.Find(What:=WhatToFind, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9013) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Searching for Line Breaks.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When you use Find and Replace, Excel normally looks through all the cells in a worksheet. You may want to limit the search to ...
Discover MoreWant to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.
Discover MoreExcel worksheets can contain all sorts of data. One thing you might store in a worksheet is a range of Boolean (TRUE or ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-12-14 16:07:44
Pat Kelso
Excellent tip. Thank you. I have been trying to figure this out for a long time!
2014-12-12 13:56:12
Ellen
super easy fix that was needed to not delay a project at work. thank you!
2013-12-14 05:48:23
Zaigham
Very Nice.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments