Removing line breaks in Excel

CHAR(13) and CHAR(10) are used for line breaks in Excel. You can take line breaks out of a cell by using the SUBSTITUTE formula:

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

. . . .in this nested version of SUBSTITUTE formula we first enter the cell to be searched, the line break code and then the character to be swapped in for the line break.

Using CHAR(10) and CHAR(13) will not always take out all of the line breaks. If this approach fails, use the CLEAN formula. The CLEAN formula takes out characters that cannot be printed. It will remove other line breaks that the CHAR(10) and CHAR(13) don't catch.

CTRL + D without formatting

The below VBA code, posted here by Scott, can be used to autofill a selected range of cells with the data in the cell above the range, but without copying the formatting from the source cell, as would be done if the common CTRL + D command were used.

1. Press ALT + F11 and paste the vba code in new module for the workbook in the project list on the left.

2. Select the cell with the data you want to copy and the cells to be filled.

3. Run the macro and the data will be copied down without the formatting from the source cell.

Sub Extend_Content_From_Above() Dim above As Range

For Each cur In Selection Set above = cur.Offset(-1, 0) above.AutoFill Destination:=Range(above, cur), Type:=xlFillValues Next cur End Sub

REPLACE formula

Don't miss how helpful Excel's REPLACE formula can be. You can use to replace X number of characters starting at a given position in a cell.

So in this example, we're replacing the three letter prefixes for the Bates numbers listed in column A. In the formula first enter the cell where the data to be replaced appears (A2); then indicate at what position the text to be replaced begins (1 - the first character in the cell); the number of characters being replaced (3); and finally the new text be entered:

=REPLACE(A2,1,3,"IBM")