How to Remove First 4 Characters in Excel

 You may have a workbook which contains text but you only want to extract certain characters from it. For example, you may only want to extract the first name from a cell which contains the first and last name of a person. In this tutorial I will show you how to manipulate text and in particular, I will show you some great techniques on how to remove the first 4 characters in Excel using the following functions:


1) Excel RIGHT Function


2) Excel MID Function


3) Excel REPLACE Function


Using the Excel RIGHT Function

In this example I want to remove the first 4 characters from the postcode CV36 7BL and leave the last 3 characters. Let's assume the postcode is in cell A2 in the Excel spreadsheet. The formula in cell B2 will be:


=RIGHT(A2,LEN(A2)-4)


So how does this formula work? Let's break this down so you can understand how it works.


RIGHT Function


The RIGHT function extracts a given number of characters from the right side of a specified text. For example =RIGHT("bananas",4) will result in "anas"


LEN Function


The LEN function extracts the length of a given string. For example =LEN("apples") will result in 6 as there are 6 characters in the string "apples".


RIGHT(A2,LEN(A2)


This section of the formula will return 8. For the first argument of the RIGHT function you have to specify what text to use. In this example it is cell A2 i.e. the postcode. For the second argument you have to specify the number of characters you want to extract. For this argument I am using the LEN function which returns the number of characters of the postcode CV36 7BL which is 8. The space between CV36 and 7BL counts as a character. The formula =RIGHT(A2,LEN(A2) translates to =RIGHT(A2,8) which returns CV36 7BL.


RIGHT(A2,LEN(A2)-4)


I want to remove the first 4 characters so therefore I include a -4 at the end of the formula. LEN(A2)-4 therefore returns 4 (8-4=4).


If I simplify this further the RIGHT function is =RIGHT(A2,4) and returns CV36.


How do you Remove the First nth Character of a String?





Comments

Post a Comment

Popular posts from this blog

Digital Marketing

 Being a Blogger is not only about writing quality content anymore, but it's also about implementing the best SEO practice, adding more value and social touch and do many more tasks . As all the bloggers have realized recently after the Google Penguin update. The importance of SEO has itself increased. There is an obvious need to change our view to SEO. And why not learn from the only website that ranks 1 for almost 99% queries. Yes, you guessed it. It is Wikipedia, the largest online encyclopedia. Whenever you want to learn some new words. Wikipedia is the place. There is no doubt, Wiki SEO is out of the box and they take care of each part of site SEO structure carefully. For us (Bloggers and SEO's) Wikipedia is a good case study to learn and understand what an effective SEO is. There are a lot of ways in which Wikipedia retains its online quality. But, it doesn't even use so much 'secret' SEO tips that you cannot figure out. Here, I have listed the SEO tricks us
Read more

The Power Behind the Internet and How and Why It Was Invented

 It is the miracle of ages. Speaking to  people around  the world in seconds and publishing one's knowledge, opinions, and thoughts for free is an immense achievement. The question is why now and how and why was it invented? To answer that we must go back at least two millennia to the first mathematical calculations and follow the path of all inventions to the present. There is another consideration, however, that is overlooked but stands out a mile in the context of a plan of the Spirit of the Universe. It is the ultimate power behind everything man does. While few hear of or contemplate this Supreme Being it is known to me. Memory of reincarnation remains the motivation for the path my life took . Between lives while in the Spirit true understanding of the purpose of our being was shown to me. We are tested for our connection to that source and everyone is back as we approach the last days. The majority are not spiritually connected and strive to achieve in wealth, power, and the
Read more