Excel easy to use the Treasure Text processing function uses the method

Source: Internet
Author: User
Tags numeric lowercase ranges alphanumeric characters

Click the easy to use tab → text processing in the cells and Ranges group to open the Text Processing dialog box.

In the Text Processing dialog box, click each button in the features area, with the appropriate options available for you to set up for the operation.

Tips

If you want to continue text processing for other areas, you can modify the selection by clicking the Select Data Area button.

Change case

All caps: Converts the English letters in each cell in the selected range to uppercase, such as Excel→excel.

All lowercase: Converts the English letters in each cell in the selected range to lowercase, such as excel→excel.

Capitalize the first letter of a word to capitalize the initials of the English word in each cell in the selected range, with the remainder in lowercase, such as excel→excel.

Capitalize the first letter of the sentence in each cell in the selected range and leave the remaining letters unchanged.

Example

Converts all uppercase letters in a cell to the first letter of the sentence, and the remaining letters in lowercase.

To do this, first convert the data area's uppercase letters to lowercase, and then capitalize the lowercase letters into the first letter of the sentence, as follows.

Select the data area, then click "Change Case" → "all lowercase" → "apply" → "capitalize" → "application".

Toggle Case: Converts the English letters in the selected range of cells to uppercase and lowercase, such as excel→excel.

Add text

With this feature, you can quickly add text data to a nonblank cell. If you select the Ignore non-text cells check box under options, numeric numbers are ignored.

Example

In the A1:a4 range, enter: "Excelhome", "120" (numeric numeric), "" (spaces, typing using a space on the keyboard), and "120" (text-type numbers).

In the position in Drop-down list box, select first character before entering "a" in the Enter text text box. Select the Ignore non-text cells check box, and click Apply, when only 120 (numeric numbers) does not add "a" before the first character, which ignores the processing of numeric numbers.

Delete text

With this feature, you can quickly delete the contents of a nonblank cell.

If you select the Ignore non-text cells check box under options, numeric numbers are ignored and text numbers are converted to numeric numbers.

Example

Enter the range of a1:a4 cells: "Excelhome", "120" (numeric number), "a" (2 spaces before "a"), and "120" (text-type digits).

In the Position Drop-down list, select Start from left and select 1 characters in the number of characters Drop-down list. Uncheck the Ignore non-text cells check box, click Apply, Ignore "120" (numeric number), and change "120" (text type Number) to "20" (Numeric number )。

Remove spaces

In the data input process, it is inevitable that the wrong input space, which will bring great trouble to the work, especially after the data contains spaces, through manual inspection is difficult to find. This feature provides 4 space-deletion features, based on common error-entry types for white space.

Remove extra spaces: Leave 2 and more contiguous spaces in the cell to only 1 spaces;

Delete Trailing spaces: Remove all spaces after the last character in the cell;

Remove leading spaces: Remove all spaces before the first character in the cell;

Remove all spaces: Delete all spaces in the cell.

Remove special characters

There are 7 options under this feature that can be used for data deletion in a variety of situations.

Non-numeric character: Removes all characters except 0~9 in a cell;

nonprinting character: Deletes all characters within a cell that cannot be recognized by the printer;

Numeric character: Deletes all characters in a cell 0~9;

Alphabetic characters: Removes all characters from A~z (a~z) in a cell;

Non-Chinese character: All characters in a cell that are outside of the language (including Chinese punctuation, such as.) ",", "etc.) deleted;

Non-numeric and non-alphanumeric characters: Removes all characters except the 0~9 number in the cell and the A~z (a~z) Letter;

Chinese characters: All of the characters in a cell (excluding English punctuation, such as.) ",", "and so on," delete.

Click the easy to use tab → text processing in the cells and Ranges group to open the Text Processing dialog box.

In the Text Processing dialog box, click each button in the features area, with the appropriate options available for you to set up for the operation.

Tips

If you want to continue text processing for other areas, you can modify the selection by clicking the Select Data Area button.

Change case

All caps: Converts the English letters in each cell in the selected range to uppercase, such as Excel→excel.

All lowercase: Converts the English letters in each cell in the selected range to lowercase, such as excel→excel.

Capitalize the first letter of a word to capitalize the initials of the English word in each cell in the selected range, with the remainder in lowercase, such as excel→excel.

Capitalize the first letter of the sentence in each cell in the selected range and leave the remaining letters unchanged.

Example

Converts all uppercase letters in a cell to the first letter of the sentence, and the remaining letters in lowercase.

To do this, first convert the data area's uppercase letters to lowercase, and then capitalize the lowercase letters into the first letter of the sentence, as follows.

Select the data area, then click "Change Case" → "all lowercase" → "apply" → "capitalize" → "application".

Toggle Case: Converts the English letters in the selected range of cells to uppercase and lowercase, such as excel→excel.

Add text

With this feature, you can quickly add text data to a nonblank cell. If you select the Ignore non-text cells check box under options, numeric numbers are ignored.

Example

In the A1:a4 range, enter: "Excelhome", "120" (numeric numeric), "" (spaces, typing using a space on the keyboard), and "120" (text-type numbers).

In the position in Drop-down list box, select first character before entering "a" in the Enter text text box. Select the Ignore non-text cells check box, and click Apply, when only 120 (numeric numbers) does not add "a" before the first character, which ignores the processing of numeric numbers.

Delete text

With this feature, you can quickly delete the contents of a nonblank cell.

If you select the Ignore non-text cells check box under options, numeric numbers are ignored and text numbers are converted to numeric numbers.

Example

Enter the range of a1:a4 cells: "Excelhome", "120" (numeric number), "a" (2 spaces before "a"), and "120" (text-type digits).

In the Position Drop-down list, select Start from left and select 1 characters in the number of characters Drop-down list. Uncheck the Ignore non-text cells check box, click Apply, Ignore "120" (numeric number), and change "120" (text type Number) to "20" (Numeric number )。

Remove spaces

In the data input process, it is inevitable that the wrong input space, which will bring great trouble to the work, especially after the data contains spaces, through manual inspection is difficult to find. This feature provides 4 space-deletion features, based on common error-entry types for white space.

Remove extra spaces: Leave 2 and more contiguous spaces in the cell to only 1 spaces;

Delete Trailing spaces: Remove all spaces after the last character in the cell;

Remove leading spaces: Remove all spaces before the first character in the cell;

Remove all spaces: Delete all spaces in the cell.

Remove special characters

There are 7 options under this feature that can be used for data deletion in a variety of situations.

Non-numeric character: Removes all characters except 0~9 in a cell;

nonprinting character: Deletes all characters within a cell that cannot be recognized by the printer;

Numeric character: Deletes all characters in a cell 0~9;

Alphabetic characters: Removes all characters from A~z (a~z) in a cell;

Non-Chinese character: All characters in a cell that are outside of the language (including Chinese punctuation, such as.) ",", "etc.) deleted;

Non-numeric and non-alphanumeric characters: Removes all characters except the 0~9 number in the cell and the A~z (a~z) Letter;

Chinese characters: All of the characters in a cell (excluding English punctuation, such as.) ",", "and so on," delete.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.