Catalog

How to Remove Characters from Right in Excel (An Easy Way)

November 9, 2023 2.3K views

Excel's character removal from the right is pivotal for streamlined data processing. It aids in data refinement, tidying up trailing spaces, and extracting pertinent insights. While this technique brings clarity to data, challenges arise in pinpointing the precise characters to eliminate. This article addresses this quandary with 5 effective approaches for removing characters from the right in Excel.

Part 1: How to Remove Characters From Right with a Formula

  • Remove Characters From Right Using LEFT

If you have city names with 6-character city codes attached, and you want only the city names, follow these steps.

Step 1: Apply the Formula

In cell B2, use =LEFT(A2, LEN(A2) - 6) to keep characters from the left side of A2 while excluding the last 6.

Step 2: Copy the Formula

Drag it down to other cells. Each cell now shows city names, omitting the 6-character codes.

  • VALUE with LEFT Function for Numeric Values

Step 1: Choose the Destination Cell

Start by selecting the cell where you want the modified value after removing right characters.

Step 2: Apply the Formula

In this example, let's focus on retaining the Order Quantity by eliminating non-numeric characters from the right. To do this, use the formula:

=VALUE(LEFT(C4, (LEN(C4) - 8)))

Step 3: Execute the Formula

Press ENTER to activate the formula.

By executing this process, the characters on the right side of cell C4 will be removed, leaving only numeric values in the Order Quantity column.

Note: Depending on the number of string characters accompanying the numeric character, you might need to adjust the formula accordingly.

Part 2: How to Remove Characters From Right with Other Easy Ways

  • Remove Characters From Right Using VBA

Step 1: Access Developer Tab

Go to Developer tab (or add it via File > More Options > Options > Customize Ribbons).

Step 2: Open Visual Basic

Click Visual Basic or use ALT+F11.

Step 3: Create Module

Insert a Module in Microsoft Visual Basic for Applications.

Step 4: Write Code

In the Module, add this code:

Function RemoveCharactersFromRight(str As String, cnt_chars As Long)

RemoveCharactersFromRight = Left(str, Len(str) - cnt_chars)

End Function

Step 5: Apply Formula

Return to the worksheet, enter formula like =RemoveCharactersFromRight(B4,5) in the desired cell.

Step 6: Get Result

Press ENTER for result. Use Fill Handle for similar cells.

  • Remove the Right Character Using Flash Fill

Step 1: Create an Example Pattern

Begin with an example pattern for Flash Fill:

Enter "Steve" to demonstrate removing right characters.

Select the cell, head to the Data tab, and choose Flash Fill.

Step 2: Apply Flash Fill

Alternatively, use the shortcut CTRL + E. Flash Fill will remove right characters based on the established pattern.

Trustpilotstars4.8
Recommend WPS Spreadsheet
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Same Excel Formula as Microsoft. Perfectly compatible with MS format.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your professional productivity by 10x with WPS's abundant free Excel templates.

5,820,008 User
avator
Algirdas Jasaitis
logo
  • Using Text to Columns Wizard

Step 1: Select the Column

Begin by selecting the column containing the text you want to separate. In this example, we'll use the "Input" column.

Step 2: Access Text to Columns

Navigate to the Data tab and choose "Text to Columns."

Step 3: Choose File Type

Select the appropriate file type for data separation. For instance, choose "Delimited" for separating first and last names.

Step 4: Select Delimiter

Choose the delimiter that separates your data. Here, we've used the Space option.

Step 5: Choose Value Type

Select the desired value type for the separated text. We've chosen "Text."

Step 6: Review and Confirm

Review the preview screen to ensure your desired outcome. Press "Finish."

Part 3: Free Office Suite for Word, Excel, PowerPoint ——WPS Office

WPS Office is a modernized powerful office tool Apps that allows you to create, edit, and share documents, spreadsheets, presentations, and PDFs on your mobile devices or computers. WPS Office is compatible with Microsoft Office and supports various file formats such as docx, xlsx, pptx, pdf, etc. WPS Office also offers cloud storage, online editing, and real-time collaboration features for users who need to work remotely or in teams.

You may choose WPS Office for the following reasons:

  • Free: WPS Office is completely free to use, even for commercial purposes.

  • Compatible: WPS Office is compatible with Microsoft Office, so you can easily open and edit Microsoft Office files.

  • Powerful: WPS Office has a wide range of features, including a powerful word processor, a spreadsheet program, a presentation program, and a database manager.

  • Modernized: WPS Office is constantly being updated with new features and functionality, making it a modern and powerful office suite.

  • AI-powered: WPS Office uses AI to help you improve your working efficiency.

Download Website: https://www.wps.com/

Frequently Asked Questions (FAQs)

1. How to extract substring of a certain length?

In Microsoft Excel, there are three functions to extract text of a defined length from cells. Depending on your starting point, consider these formulas:

LEFT function: Extracts a substring from the left.

RIGHT function: Extracts text from the right.

MID function: Extracts a substring from the middle of a text string, commencing at your designated point.

2. How to remove text in excel by using find and replace function?

To efficiently remove unnecessary data in Excel, adhere to the following steps: First, select the cells containing the undesirable text. Then, access the find and replace dialog by simultaneously pressing Ctrl + H. Input the text you intend to replace within the "Find what" box and leave the "Replace with" box vacant. Finally, hit Enter.

Summary

To sum it up, mastering character removal from the right in Excel is crucial for efficient data management. This guide has unveiled five effective techniques to enhance data quality and refine your content.

Moreover, the contemporary powerhouse, WPS Office, offers a versatile solution for creating, editing, and sharing documents, slides, and spreadsheets across devices. Its compatibility, robust features, and AI integration make it a compelling choice for modern productivity.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.