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. Show
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. Download Excel Workbook5 Easy Ways to Remove Space between Rows in ExcelRemoving 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 Textoption 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. Read More: How to Remove Leading Spaces in Excel (4 Methods) Method 2: Using TRIM Function to Remove Space between RowsWe know the TRIM function removes all kinds of excessive spaces. The syntax of the TRIM function is 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. 🔼 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. Read More: How to Remove Trailing Spaces in Excel (6 Easy Methods) Method 3: Replacing Cell Format with Existing Ones Using Find and Replace FeatureEarlier 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. Read More: How to Remove Space after Number in Excel (6 Easy Ways) Method 4: Using Excel SUBSTITUTE Function to Remove Space between RowsThe 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
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).
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 (6 Quick ways) Method 5: Using CLEAN Function to Remove Space between RowsOne of the alternative ways to get rid of extra any kind of space and link break is the CLEAN function. The syntax of the function is 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. Step 2: Move the Fill Handle to fetch the space removed entries in new cells as depicted in the below picture. 🔺 Issues with Removing SpaceCertain 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. 🔼 Afterwards you can use the entries in any calculation without facing any difficulties. ConclusionIn 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
|
