How to delete the first character in an excel spreadsheet cell

Often, users of the Excel spreadsheet editor face such a task as deleting the first character in a table cell. You can implement this procedure using various methods, for example, using special integrated operators. In the article, we will consider in detail, using examples, several methods that implement the removal of characters in a cell of tabular data.

Delete first character in excel spreadsheet

To implement this simple procedure, a special integrated function is used. Detailed instructions for removing the first character look like this:

1. For example, we have such a plate containing a certain set of data on the workspace of a spreadsheet document. We need to implement the removal of the first character.

2. Initially, we need to identify the total number of characters in all cells. To perform this action, you must use the DLSTR operator. This function allows you to count the number of characters. Move the cursor to cell B2 and select it with the left mouse button. Here we drive in the following formula: =DLSTR(A2). Now we need to copy this formula to the bottom cells. Move the mouse pointer to the lower right corner of the field B2. The cursor has taken the form of a small plus sign of a dark shade. Hold LMB and drag the formula to the rest of the cells.

3. At the next stage, we proceed to remove the 1st character on the left. To implement this procedure, an operator called RIGHT is used. Move the cursor to cell B2 and select it with the left mouse button. Here we drive in the following formula: =PRAWSIMV(A2;DLSTR(A2)-1). In this formula, A2 is the coordinate of the cell where we are removing the first character from the left, and LT(A2)-1 is the number of characters returned from the end of the line on the right side.

This figure for each field is calculated by subtracting one character from the total number of characters.

4. Now we need to copy this formula to the bottom cells. Move the mouse pointer to the lower right corner of the field B2. The cursor has taken the form of a small plus sign of a dark shade. Hold LMB and drag the formula to the rest of the cells. As a result, we have implemented the removal of the first character on the left of each selected cell. Ready!

Additionally, you can use a special operator called PSTR. For example, we have data in the cells of which the serial number of employees is indicated. We need to remove the first characters before a dot or a space. The formula will look like this: =MID(A:A;SEARCH(“.”;A:A)+2;DLSTR(A:A)-SEARCH(“.”;A:A)).

Removing a character before a character in a spreadsheet editor

There are situations when it is necessary to delete characters up to a certain character in a spreadsheet document. In this case, the following simple formula applies: =REPLACE(A1,SEARCH(“character”,A1),). Result of transformations:

• A1 is the field that is being checked.
• A character is an object or textual information to which the cell will be trimmed to the left.

Additionally, this procedure can be combined with data cleaning “After”.

Deleting a character before a comma in a spreadsheet editor

There are situations when it is necessary to remove decimal places in a spreadsheet document. In this case, the following simple formula applies: =REPLACE(A1;1;SEARCH(“&”;A1);). Result of transformations:

Removing characters up to a space in a spreadsheet editor

There are situations when it is necessary to delete characters up to a space in a spreadsheet document. In this case, the following simple formula applies: =REPLACE(A1;1;SEARCH(“&”;A1);). Result of transformations:

Removing with the SUBSTITUTE operator

Removing characters can be done with a simple statement called SUBSTITUTE. General view of the operator: =SUBSTITUTE(text, old_text, new_text, entry_number).

• Text – here the field with the data to be changed is set.
• Old_text is the data that will change.
• New_text – data that will be inserted instead of the original.
• entry_number is an optional argument. It allows you to replace characters starting with a specific number.

For example, if we need to implement the removal of points located to the left of the main text, then we need to enter the following formula: =SUBSTITUTE(A1;”.”;” “).

Using this formula, we will replace the given character, written to the left of the main text, with spaces. Now we need to implement the removal of these spaces. To implement this procedure, an operator is used, which has the name TRIM. The function allows you to find unnecessary spaces and remove them. The general view of the operator looks like this: =TRIMSPACES().

Important! This formula only removes normal spaces. For example, if the user added information copied from some site to the worksheet, then it may not contain spaces, but characters similar to them. In this case, the TRIM operator will not work for deletion. Here you will need to use the Find and Remove tool.

Deleting with the CLEAN operator

Optionally, you can apply the PRINT operator. The general view of the operator for removing non-printable characters looks like this: =CLEAN(). This function removes non-printing characters in a line (line breaks, paragraph characters, various squares, and so on). The operator is necessary in cases where it is required to implement the removal of a line break.

Important! The operator removes only most of the extra characters.

Conclusion and conclusions about the removal of the first characters

We have considered methods for removing the first character from tabular information. Methods imply the use of integrated operators. Using functions allows you to significantly speed up the process of working with large amounts of tabular information.