How to Remove the First Two Characters in Excel: Quick Tips

SenseBridge

2 min read

·

24-10-2024

1

0

Share

Removing the first two characters in Excel can be a straightforward task if you know the right methods to use. Whether you’re cleaning up your data for analysis or simply need to format text correctly, this guide will provide you with effective techniques to achieve this. Let’s dive into some quick tips and techniques!

Using the MID Function 📏

One of the most effective ways to remove the first two characters from a string in Excel is by using the MID function. This function allows you to extract a substring from a text string based on specified starting position and length.

Syntax of the MID Function:

=MID(text, start_num, num_chars)

• text: The text string from which you want to extract characters.
• start_num: The position of the first character to extract (1-based index).
• num_chars: The number of characters to return.

Example:

If you have the string "Hello World" in cell A1, and you want to remove the first two characters "He", you can use:

=MID(A1, 3, LEN(A1)-2)

This formula starts extracting from the third character and continues to the end of the string.

Using the RIGHT Function ➡️

Another simple method is to use the RIGHT function, which allows you to extract a specified number of characters from the end of a string.

Syntax of the RIGHT Function:

=RIGHT(text, [num_chars])

• text: The text string from which you want to extract characters.
• num_chars: The number of characters to return from the right.

Example:

To achieve the same result using the RIGHT function, you can subtract 2 from the total length of the string. For "Hello World" in A1, use:

=RIGHT(A1, LEN(A1)-2)

Using Text to Columns for Bulk Changes 📊

If you need to remove the first two characters from a large dataset, you can use the Text to Columns feature.

1. Select the column containing the data you want to modify.
2. Go to the Data tab on the Ribbon.
3. Click on Text to Columns.
4. Choose Delimited and click Next.
5. Deselect all delimiters and click Next.
6. In the Column Data Format, select Text.
7. In the destination field, use a formula like:

   =MID(A1, 3, LEN(A1)-2)
   

8. Finish the wizard, and it will apply your changes to the entire column.

Important Note: "Using Text to Columns will overwrite your original data, so consider making a copy of your data before proceeding."

Quick Tip: Use Flash Fill ✨

Excel’s Flash Fill feature can automatically fill in values based on the patterns you provide. Here’s how to use it:

1. In the next column next to your data, manually type the expected result (without the first two characters).
2. Start typing the next expected result; Flash Fill will start suggesting the rest.
3. Simply hit Enter to accept Flash Fill's suggestions.

Summary of Functions

By utilizing the techniques outlined above, you can efficiently remove the first two characters from text strings in Excel. Remember to choose the method that best fits your data situation, whether it be a single cell, a range, or an entire dataset. Happy Excel-ing! 🎉