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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset-Remove Space in Excel Between Rows

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.

Wrap text-Remove Space in Excel Between Rows

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

Unselecting Wrap Text

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

Wrap Text result

Read More: How to Remove Leading Spaces in Excel


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)

TRIM Function-Remove Space in Excel Between Rows

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

Fill Handle

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

Final result


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.

Find & Replace-How to Remove Space in Excel Between Rows

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.

Find and Replace window

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

Final outcome

🔺 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.

Find and Replace using cell format

Read More: How to Remove Space after Number in Excel


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.

SUBSTITUTE Function-Remove Space in Excel Between Rows

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

Fill handle

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

Final result

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)

Clean function-Remove Space in Excel Between Rows

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

ResultRead 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.

Issue-Remove Space in Excel Between Rows

🔼 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)

VALUE Function result

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

Final result


Download Excel Workbook


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo