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

In the workplace office, we often encounter some non-standard forms, such as useless "blank lines" and "blanks", which we often take some time to delete. Is there any good way to deal with these useless "blank lines" and "spaces" in batches?

1. Find and replace to clear spaces

Select the data area, then press the "Ctrl + H" key combination to open the "find and replace" dialog box, enter a space in "find what", and replace them all.

2. SUBSTITUTE function

The SUBSTITUTE function replaces the specified text in a text string. Here, we can use this function to replace spaces.

= SUBSTITUTE (data region, old text to be replaced, text to be replaced)

= SUBSTITUTE (A2, ",")

3. TRIM function

The TRIM function removes the front and back spaces in the cell, leaving a space between the characters.

= TRIM (A2)

4. CLEAN function

If there is a line break between the data, how can we clear it? This is shown in the following figure.

Using the CLEAN function, you can clear all line breaks.

= CLEAN (A2)

After clearing spaces or line breaks in the data through the formula, we cannot use it directly. We need to copy the data first, and then paste it as a "numeric value" to be able to use it normally.

Above we talked about batch deletion of useless spaces, and let's talk about how to delete useless blank lines in batches.

1. Positioning method

Select the table, then press the "F5" key, select "location condition"-"null value", and then right-click to delete all excess blank lines.

2. Sorting method

Select the table, then enter "data"-"sort and filter"-"sort", select the order, and finally click OK.

3. Screening method

Select the data, enter the "data"-"sort and filter"-"filter", click the drop-down triangle button on the "date", cancel the check box in front of "Select all", then check "blank", delete the blank line in the filter results of the table, finally, we enter the date again, check "Select all" on OK.

This article comes from the official account of Wechat: Word Alliance (ID:Wordlm123), author: Yi Xuelong

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.