Written by Allen Wyatt (last updated September 2, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
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:
Sub FindLineBreak() Cells.Select Selection.Find(What:=Chr(10), After:=ActiveCell, _ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3220) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Searching for Line Breaks.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Searching for information in an Excel worksheet generally goes very smoothly. There can be times, however, when the ...
Discover MoreWhen you use Find and Replace, Excel normally looks through all the cells in a worksheet. You may want to limit the ...
Discover MoreFinding and replacing information in a worksheet is easy. Finding and replacing in other objects (such as text boxes or ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-10-07 20:01:02
A J Hawley
You can also search for the VB constant vbLF.
2015-03-11 07:13:56
dickjones
yes there is.
2015-02-06 19:46:52
Eirik
hi. I need to insert a line break before an word. Its always diffrent words but they always ends with a : is there any way to do this?
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments