Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

There are both spaces and blank lines in Excel. How to delete only blank lines?

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > IT Information >

Share

Shulou(Shulou.com)11/24 Report--

For cousins who often need to copy data online, it is very troublesome to delete only blank lines if there are both spaces and blank lines in these tables.

Because the commonly used "locate null value-delete" method is invalid! In this way, it will be both! Department! Delete it! Except! (the spaces here refer to discontiguous blank cells; blank lines are lines full of spaces. )

So, do we have to go back to "manually delete one or more rows"? Not only tired, but also inefficient.

Of course. No!

Excel is an efficiency tool, efficient operation is normal.

Continue to look down and teach you two ways to solve the problem easily!

Case study for example, in the following table, there are a lot of spaces and blank lines that require the deletion of yellow blank lines and the retention of spaces.

Two solutions are:

❶ function method

❷ shortcut key method.

Next, let's take a look at the specific operation.

02 solution ▋ function method

Here, the function we need to use is Counta, which is used to calculate the number of cells in the range that are not empty.

This function is very simple, and its basic syntax is:

= COUNTA (value1, [value2],...)

Parameters refer to the values that need to be counted, such as A2:C2 in this article.

Method of operation:

❶ adds a secondary column to count the number of non-empty cells.

The data region is in column ARV C, so enter the following formula in the D2 cell and drop down to fill it.

= COUNTA (A2:C2)

❷ filters D and deletes rows with a result of 0.

Select the data area, click * * start * *-> * * sort and filter * *-> * * filter * *. A filter button appears in the first row of the data.

Click the filter button in the D1 cell, select 0, and OK

Select this area, right-click "Delete", filter and select "Select all".

PS: filter "blank" directly, and you can also delete blank rows, but may also delete reasonably existing blank cells, so it is recommended to use secondary column counting.

▋ shortcut key method

This method is very simple and can be easily solved with only 4 keyboard shortcuts.

They are:

[Ctrl +\]

[Ctrl+9]

[Alt+;]

[Ctrl+Shift+ (]

Method of operation:

❶ selects a non-blank line.

Select the data area and press [Ctrl+\]

(you can also locate "row content difference cells" ~)

❷ hides non-blank lines.

Press [Ctrl+9]

❸ deletes the visible line.

Box to select all blank lines, press [Alt+;] to select visible lines, and then right-[Delete]-"whole line"

❹ unhides rows.

Select the column where the data is located, and press [Ctrl+Shift+ (])!

03 to sum up, there are both spaces and blank lines, and only blank lines are deleted. This article describes a total of two methods. To put it simply, they are:

❶ function method-- Counta function

❷ shortcut method:

[Ctrl +\]

[Ctrl+9]

[Alt+;]

[Ctrl+Shift+ (]

Here, Xiao E has a sweet little Tips:

If there is no response by pressing [Ctrl+Shift+ (]), it may be a hotkey conflict ~

At this point, you can click the [start] tab-[format]-[hide and unhide], find "unhide rows" inside, and click!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: witty Qiu Xiao E

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

IT Information

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report