# How to Remove Space between Rows in Excel (5 Methods)

In Excel, sometimes users insert unnecessary space (that can be Line Break or else) between rows. Therefore, we have to go through the hassle to remove space in Excel between rows. Users may have entered a line break using ALT+ENTER (i.e., CHAR(10)) resulting in space between rows. Excelâ€™s unselecting Wrap Text and Find & Replace features, TRIM, and SUBSTITUTE functions easily remove space between rows.

Letâ€™s say, we have a dataset where some of the entries are entered with space between rows. Apparently, the space between the rows is Link Break. And we need to remove them.

In this article, we demonstrate the ways to remove space in Excel between rows.

## How to Remove Space between Rows in Excel: 5 Easy Ways

Removing space between rows is not totally similar to removing Leading, Trailing, or other spaces. As Link Break results in inserting space between rows, we need to apply methods similar to removing Link Break. Follow the below section to achieve it.

### Method 1: Remove Space between Rows Using Wrap Text

🔼 If you select the cells that have space between rows, you see the Wrap Text option is enabled from the Alignment section in the Home tab.

🔼 Unselect the Wrap Text option from Home > Click on Wrap Text.

🔼 In a moment, Excel removes all the space between rows as depicted in the below image.

### Method 2: Using TRIM Function to Remove Space between Rows

We know the TRIM function removes all kinds of excessive spaces. The syntax of the TRIM function is

`TRIM (text)`

The text argument refers to the cell reference from where you want to remove the space.

🔼 Use the below formula in any blank cell to get space removed entries as shown in the following image.

`=TRIM (B5)`

🔼 To apply the formula in other cells, use the Fill Handle first in the below cell.

🔼 Then, use the Fill Handle to the right-side cell to remove space between rows.

### Method 3: Replacing Cell Format with Existing Ones Using Find and Replace Feature

Earlier we said that typical link breaks appear as space between rows. As a result, we can use CTRL+J to find Link Break or CHAR(10) within the worksheet.

🔼 Go to Home > Select Find & Select (from the Editing section) > Click on Replace.

You can directly bring out the Find and Replace window using Keyboard Shortcuts CTRL+H.

🔼 Excel fetches the Find and Replace window. In the Find and Replace window,

â§« Insert CTRL+J in the Find What command box. CTRL+J is used for inserting Link Break Character in the Find and Replace window.

â§« Keep the Replace with command box empty.

â§« First- Click on Find All.

â§« Second- Click on Replace All.

🔼 Executing Replace All removes space between rows similar to the below picture.

🔺 Alternatively, you can assign Cell Formats to Find what and Replace with command box by clicking the down arrow beside Format > Selecting Choose Format From Cell Format option.

### Method 4: Using Excel SUBSTITUTE Function to Remove Space between Rows

The Link Break is a character that can be easily replaced by the SUBSTITUTE function. The SUBSTITUTE function takes text as cell reference and specifically removes given old_text with new ones. The syntax of the SUBSTITUTE function is

`=SUBSTITUTE (text, old_text, new_text, [instance])`

The arguments define

text; The cell reference users want to change.

old_text; The characters or text have to replace within the text.

new_text; The new text users want to replace with.

instance; The number of replacements users want to execute. In case of not mentioning, replaces all the instances. [Optional]

Step 1: Write the below formula in any blank cell (i.e., F5).

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

Comparing the formula to the syntax, B5 = text, CHAT(10) = old_text,“” = new_text. Here, CHAR(10) represent the Link Break.

Step 2: Drag the Fill Handle downward to remove space between rows.

Step 3: Again, use the Fill Handle to fetch the entries in other cells without Line Break or Space between rows.

Read More: How to Remove Space in Excel after Text

### Method 5: Using CLEAN Function to Remove Space between Rows

One of the alternative ways to remove any kind of extra space and link break is the CLEAN function. The syntax of the function is

`CLEAN (text)`

Here text is the cell reference from where you want to remove the space between rows.

Step 1: To clean the space between rows, write the following formula in any blank cell.

`=CLEAN (B5)`

Step 2: Move the Fill Handle to fetch the space removed entries in new cells as depicted in the below picture.

Read More: How to Remove Tab Space from Excel

## 🔺 Issues with Removing Space

Certain functions such as SUBSTITUTE, return entries in different formats after their execution. If we use those resultant outcomes, we encounter calculation errors (like getting product value 0) as shown in the below picture.

🔼 To overcome the caveat, use the VALUE function. The VALUE function converts any cell data type (mostly text) into calculatable entries. Insert the below formula to transform texts into values as shown in the below image.

`=VALUE(C5)`

🔼 Afterwards you can use the entries in any calculation without facing any difficulties.

## Conclusion

In this article, we demonstrate features and functions to remove space in Excel between rows. In case of further calculation, itâ€™s better not to use Wrap text or TRIM to remove space since they pose data format issues. SUBSTITUTE functionâ€™s outcomes need transformation text to value to be used in further operations. Other functionâ€™s resultant values work just fine. Hope you find these above-mentioned methods excel in your case. Comment, if you have further inquiries or have anything to add.

## Related Articles

<< Go Back To Remove Space in Excel | Data Cleaning in ExcelÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF