Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How To Delete All Blank Lines Or Only The First Line In Cell In Excel ?
Microsoft Excel is a potent spreadsheet programme that enables you to efficiently organise and analyse data. You may encounter situations while working with huge datasets or text within cells where you need to eliminate blank lines or just the first line to improve the readability and presentation of your data.
In this article, we'll look at two typical situations− removing all blank lines from a cell and removing just the first line. You will be given step−by−step guidance on how to carry out these activities utilising various Excel−compatible techniques. Regardless of your level of Excel proficiency, this video will enable you to streamline your data and save time. So, let's get started and discover how to eliminate just the first line or all blank lines in an Excel cell!
Delete All Blank In Cell
Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to know how you can delete all blank lines or only the first line in a cell in Excel.
Step 1
Consider an Excel sheet where you have a list of cells containing multiple lines, similar to the below image.
First, right−click on the sheet name and select View Code to open a VBA application. Then, click on Insert and select Module, then copy the below code into the text box.
Right click > View code > Insert > Module > Copy.
Code
Sub DoubleReturn()
Dim xRng As Range, xCell As Range
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Range of cells", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
For Each xCell In xRng
For I = 1 To Len(xCell) − Len(WorksheetFunction.Substitute(xCell, Chr(10), ""))
xCell = Replace(xCell, Chr(10) + Chr(10), Chr(10))
Next
Next
End Sub
Step 2
Then save the sheet as a macro−enabled template, click F5 to run the module, select the range of cells, and click OK to complete our task.
Save > F5 > Cells > Ok.
Note
To delete only the first line, you can use the below code.
Code
Option Explicit
Sub RemoveFirstLine(ByRef Target As Range)
Dim xCell As Range
For Each xCell In Target.Cells
xCell.Value = Right(xCell.Value, Len(xCell.Value) − InStr(1, xCell.Value, vbLf))
Next
End Sub
Sub StartRemove()
Dim xRng As Range
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Range of cells", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
RemoveFirstLine xRng
End Sub
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can delete all blank lines or only the first line in a cell in Excel to highlight a particular set of data.
67 Views
