How To Remove First Character In Excel

A big part of processing data involves the cleaning of the data.

Well-nigh data you bring in to Excel ofttimes comes from various sources. Many times they are converted from a different format. As such, it is non uncommon to find unwanted characters inside text cord information.

What'southward more than annoying is that sometimes these unwanted characters are invisible. And so they fail to give expected results when operations are performed on them. Another mutual problem is the presence of inconsistent blank spaces.

In this tutorial, we will address all the in a higher place issues using Excel functions, formulas, and features. Nosotros will see how to use various Excel functionalities to remove specific characters from strings in your cells.

• Removing a Specific Character with the Notice and Supplant Feature
• Removing a Specific Character with the SUBSTITUTE Function
• Removing just a Item Instance of a Specific Grapheme in a String
• Removing any Special Character with the Make clean Function
• Removing Leading or Trailing Infinite Characters with the TRIM Office
• Removing a Specific Invisible Graphic symbol from a String using SUBSTITUTE, CHAR and CODE Functions

Removing a Specific Character with the Notice and Supervene upon Feature

Excel's Find and Replace dialog box is a dandy fashion to discover items on your worksheet and go things done apace.

All you lot demand to do is enter your search string to specify what you want to supervene upon and so specify what you desire to supplant it with.

Suppose y'all have the below dataset and you want to remove all the '@' characters from the text string in each cell.

Below are the steps to remove a specific grapheme using Find and Replace:

1. Select the range of cells you lot want to work with.
2. Click on Discover & Select from the Dwelling tab (under the 'Editing' group).
3. This will display a dropdown menu. Select 'Replace'.
4. This will open up the Find and Supervene upon dialog box. Type '@' in the text box next to 'Find what'.
5. Leave the text box next to 'Replace with' bare. This is considering you lot want to remove any example of the '@' symbol in each cell.
6. Click on the 'Replace All' button.

This will remove all instances of the '@' symbol from all the cells.

Removing a Specific Character with the SUBSTITUTE Function

The SUBSTITUTE function can be used to remove a specific character from a cord or replace it with something else. The general syntax for this function is:

=SUBSTITUTE (original_string,                old_character,                new_character,                instance_number)

Here,

• original_string is the text or reference to the prison cell that you want to work on.
• old_character is the character that y'all want to supercede.
• new_character is the character that you want to replace old_character with.
• instance_number is optional. It is the instance of the old_character that you want to supplant.

It is possible to customize the in a higher place formula to the make it suitable to remove a specific character from a cord, as follows:

=SUBSTITUTE (original_string,                old_character,                "")

This formula volition replace the old_character with a blank (""), which means the character volition basically get deleted.

Let the states presume you have the aforementioned set of string values with the '@' symbol in random places, and you want to remove all of them:

For this, you can utilise the SUBSTITUTE function with the following steps:

1. Select the showtime cell of the column where you lot desire the results to appear. In our instance, information technology will be cell B2.
2. Type the formula:

   =SUBSTITUTE(A2,"@","")

3. Printing the return key.
4. This will give you the text obtained after removing all instances of the '@' symbol in prison cell A2.
5. Double click the fill up handle (located at the bottom-left corner) of cell B2. This will copy the formula to all the other cells of column B. Y'all tin also cull to drag down the fill up handle to attain the same effect. Here are both the original and converted columns side by side:
6. If you want to retain only the converted versions of the text, and then select these cells (B2:B5), copy them, and paste them in the same place as values.
7. You tin then delete column A if you demand to.

Removing simply a Particular Instance of a Specific Character in a Cord

Now, what if you wanted to remove just the first '@' symbol from each cell, instead of all instances of them?

This is where the last optional parameter of the SUBSTITUTE function comes in handy.

Using this, yous tin specify which instance of the symbol you want to remove. So, to remove the starting time instance of a symbol, your office should be:

SUBSTITUTE (original_string,                old_character,                "",1)

Similarly, if y'all want to remove the 2d example of the character, the role volition be:

SUBSTITUTE (original_string,                old_character,                "",2)

Allow's meet the steps to remove only the get-go instance of the '@' symbol from the higher up dataset:

1. Select the showtime cell of the column where yous desire the results to appear. In our example, it will be jail cell B2.
2. Type the formula:

   =SUBSTITUTE(A2,"@","",ane)

3. Press the return key.
4. This will give yous the text obtained after removing simply the commencement '@' symbol in jail cell A2.
5. Double click the fill handle (located at the bottom-left corner) of cell B2. This will re-create the formula to all the other cells of cavalcade B. You tin as well choose to drag down the fill up handle to achieve the same effect. Hither are both the original and converted columns side by side:
6. If y'all want to retain but the converted versions of the text, and so select these cells (B2:B5), copy them, and paste them in the same place as values.
7. Yous tin then delete cavalcade A if you demand to.

Removing any Special Character with the Make clean Function

The Excel CLEAN part removes line breaks and non-printable characters from a cord. The general syntax for this function is:

=Clean (original_string)

Hither, original_string is the text or reference to the text cell that you want to clean.

The result is the text that has all non-printable characters removed.

Permit's take a look at the following set of strings.

Since the list was brought in from some other application, it ended up having a lot of unnecessary characters, like new-line characters, spaces, etc.

Let'due south run across how to employ the Make clean function to make clean this data:

1. Select the first jail cell of the column where you want the results to appear. In our case, it will exist cell B2.
2. Type the formula:

   =Make clean(A2).

3. Printing the return cardinal.
4. This will requite you the text obtained later on removing all line breaks from the string in cell A2.
5. Double click the fill handle (located at the lesser-left corner) of cell B2. This volition re-create the formula to all the other cells of column B.

Discover that the new line characters got removed, but the results all the same don't look right. This is because when the data was brought in, information technology too contained some space characters, as well the new lines.

The Make clean function removes simply the first 32 (non-printable) characters in the 7-bit ASCII code (i.e. values 0 to 31). Still, there are other non-printable characters in Unicode that CLEAN cannot remove.

Since the space character has a value of 32, the CLEAN function does not remove spaces. And so information technology is all-time to apply the TRIM function after applying the CLEAN function to remove the spaces.

Removing Leading or Abaft Space Characters with the TRIM Function

A lot of data cleaning merely consists of removing leading or trailing space characters from strings. Excel'southward TRIM function makes this easy to exercise in but i go.

The TRIM office removes the space graphic symbol (" ") from the text. If the spaces are leading or trailing spaces, it removes all of them. If in that location are actress spaces betwixt words, then it removes the extras and leaves just a single space.

The general syntax for this function is:

=TRIM (original_string)

Here, original_string is the text or reference to the text cell that you want to process.

Let us use the TRIM office to remove the space characters that were left over after applying the CLEAN function:

1. Select the start cell of the column where yous want the results to appear. In our case, it will be cell C2.
2. Type the formula:

   =TRIM(B2).

3. Press the render key.
4. This will give you the text obtained after removing all unnecessary spaces from the cord in cell B2.
5. Double click the make full handle (located at the bottom-left corner) of cell C2. This will copy the formula to all the other cells of column C.
6. If you want to retain simply the converted versions of the text, so select these cells (C2:C5), copy them, and paste them in the same identify as values.
7. You lot tin can then delete columns A and B if you need to.

Removing a Specific Invisible Character from a String using SUBSTITUTE, CHAR and Code Functions

In some cases, both CLEAN and TRIM functions neglect to remove some particularly abrasive characters from the string.

This may be because these characters are neither spaces nor one of the 32 characters that the Make clean function tin can remove.

Y'all can discover the lawmaking of a character by using the CODE function. For example, in the sample text below, there is an invisible character at the start of the string.

Since it couldn't be removed with a TRIM or CLEAN, information technology is quite evident that it'southward non a regular space.

Here'due south what we can practise to remove all instances of the invisible character:

1. To find out what this graphic symbol is, nosotros can utilize the Code function. Blazon this function into the cell B2: =Lawmaking(LEFT(A2)). Since the character is in the first position in the text, nosotros can easily detect out its code using the LEFT part. In this case, we get the outcome equally "160". That means the invisible graphic symbol's code is 160.
2. Let us apply this value in the SUBSTITUTE role. Blazon this function into the prison cell C2: =SUBSTITUTE(A2,CHAR(B2),""). Here nosotros used the CHAR part to convert the character code back to its graphic symbol equivalent.
3. When you press the return cardinal now, yous will detect all instances of that invisible character removed from the string.

In this tutorial, we saw how you can use diverse Excel functions, formulas, and features to remove specific characters from a string.

If you lot know what the character you lot want to remove is, you can use either the Detect and Replace feature or the SUBSTITUTE function.

To remove blank spaces and special characters (that often accompany data brought in from other applications) you can utilize the Make clean and TRIM functions respectively.

If in that location are other invisible characters in the cord and yous don't know what the characters are, you tin can employ a formula that combines the Lawmaking, CHAR, and SUBSTITUTE functions together.

We tried to put together all possible situations where you lot would need to remove a specific graphic symbol from text in Excel.

Hope you lot institute the tutorial useful!

Other Excel tutorials you lot may similar:

• How to Remove Text after a Specific Character in Excel
• How to Remove Commas in Excel (from Numbers or Text Cord)
• How to Remove Dollar Sign in Excel
• How to Remove Apostrophe in Excel
• How to Add together Text to the Beginning or End of all Cells in Excel
• How to Reverse a Text Cord in Excel (Using Formula & VBA)
• How to Change All Caps to Lowercase Except the Starting time Alphabetic character in Excel?
• How to Extract Text After Space Graphic symbol in Excel?
• Extract Last Name in Excel

Source: https://spreadsheetplanet.com/remove-specific-character-from-string-excel/

Posted by: sandersbestinge1986.blogspot.com

Share this post