Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Pull or Extract Characters from Right to Left in a Cell Until a Space is Reached in Excel?
Sometimes, in Excel the user wants to extract data for the provided task, the extraction will be from right to left. For example, if a cell contains data “Ana Ban” then the extracted string will be “Ban”. To demonstrate the formula used to accomplish this task will provide a simple example. All explanations are guided in a detailed stepwise manner. The first example guides the process of using the formula to perform the same task.
Example 1: To pull or extract characters from right to left in a cell until a space is reached in excel by using the user-defined formula.
Step 1
To understand the process of extracting characters from right to left from a cell until a space is reached in Excel, will assume the below given Excel sheet. This Excel sheet contains two columns. The first column contains data for the complete name, while the second column contains space to store the extracted right to left character of data provided in the B column. Consider the data provided from the first column which is the complete name column. And the second column specifically stores the last name or sir names of the user.
Step 2
Go to the C2 cell, and type formula “=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",255)),255))”. Consider the below-depicted image for reference:
Explanation for formula
SUBSTITUTE (B2, " ", REPT (" ", 255)): This part of function replaces every space (" ") with the text string in B2 cell with a repeated string of 255 spaces. The main purpose of using REPT (" ", 255) is to ensure that the repeated string is longer than any possible word in the text. The result of this step is to modify the string with elongated spaces.
RIGHT (modified string, 255): The RIGHT function extracts the rightmost 255 characters from the modified string obtained in step 1. This is done to ensure that user can capture the last word completely.
TRIM (rightmost 255 characters): The TRIM function removes any leading or trailing spaces from the rightmost 255 characters obtained in step 2. This step is compulsory as the repeated spaces added in step 1 might introduce additional trailing spaces.
Step 3
Press the “Enter” key. This will generate the required result which is the first name, of the C2 cell, as depicted below:
Step 4
From the C2 cell drag the fill handle to copy same formula to other rows, and the final obtained results are shown below:
Conclusion
This article briefly describes the implementation of the task by providing a stepwise explanation for each example. All the guided steps are unique and illustrated through screenshots and can justify an easy method to perform the same task. After learning all the steps user can easily be able to extract or pull a character from the first column, and the evaluated result will be stored in the second column.
178 Views
To Continue Learning Please Login