Unless you’re among the few lucky ones, you’ll most likely get your data in a format that would need some cleaning. One common use case of this would be when you get a dataset and you have to remove some characters from the left for this dataset. These could be a fixed number of characters that you need to remove from the left, or could be before a specific character or string. In this tutorial, I will show you some simple examples of removing the required number of characters from the left of a text string.
Removing Fixed Number of Characters from the Left
If you get a dataset that is consistent and follow the same pattern, then you can use the technique shown here to remove a fixed number of characters from the left of the string in each cell. Below I have a dataset where I have the product ids, which consist of a two-letter code followed by a number, and I want to extract only the number in each cell (which means that I want to remove the first three characters from each cell). Below is the formula to do this: The above formula uses the LEN function to get the total number of characters in the cell in column A. From the value that we get from the LEN function, we subtract 3, as we only want to extract the numbers and want to remove the first three characters from the left of the string in each cell. This value is then used within the RIGHT function to extract everything except the first three characters from the left. Since we have hardcoded the number of characters we want to remove from the left, this method would only work when you always want to remove the fixed number of characters from the left. If, in the above example, we have inconsistent data where there are varying numbers of characters before the number, we would not be able to use the above formula (use the formula next section in such a scenario).
Removing Characters from the Left based on Delimiter (Space, Comma, Dash)
In most cases, you’re unlikely to get consistent data where the number of characters you want to remove from the left would be of fixed length. For example, below I have the names dataset where I want to remove the first name and only get the last name. And as you can see, the length of the first name varies, so I can not use the formula covered in the previous section. In this case, I still need to rely on a consistent pattern – which would be a space character that separates the first and the last name. If I can remove everything till the space character, I would get the desired result. And thanks to awesome functionalities in Excel, there are multiple ways to do this.
Using the RIGHT Formula
Let’s first have a look at a formula that will remove everything before the space character and you will be left with the last name only. The above formula will remove everything to the left of the space character (including the space character), and you will get the rest of the text (last name in this example). Let me quickly explain how this formula works. First, I have used the FIND function to get the position of the space character in the cell. In the above formula, FIND(” “,TRIM(A2))) would return 6 as the space character occurs at the sixth position in the name in cell A2. I then used the LEN function and subtracted the value that the FIND function gave me to get the total number of characters after the space character in the cell. And now that I know how many characters to extract from the right of the text string, I’ve used the RIGHT function to extract it. Note: In the above formula, I have used the TRIM function to make sure that any leading, trailing, or double spaces are taken care of. One big benefit of using a formula is that the results automatically update in case you make any changes in the data in Column A.
Using Flash Fill
Another really fast way to quickly remove text from the left of the delimiter is by using Flash Fill. Flash Fill works by identifying patterns from a couple of inputs from the user. In our example, I would have to manually enter the expected result for one or two cells, and then I can use Flash Fill to follow the same pattern for all the other remaining cells. Suppose I have a dataset as shown below where I want to remove all the characters before the space character. Below are the steps to use flash fill to remove characters from the left of a delimiter: The above steps would remove everything from the left of the space character and you will be left only with the last name. Note that Control + E (or Command + E in Mac) is the keyboard shortcut for Flash Fill in Excel. Now let me quickly explain what’s happening here. When I manually enter the expected result in cell B2, and then select all the cells and use Flash Fill, it tries to identify the pattern using the result I’ve already entered in cell B2. In this example, it was able to identify that I am trying to extract the last name, which means that I’m trying to remove everything which is there on the left of the last name. Once it was able to identify this pattern, it applied it to all the cells in the column. In some cases, it is possible that Excel will not be able to identify the correct pattern when using Flash Fill. In such situations, you can try entering the results in more than one cell so that excel has more data to understand the pattern. For example, you can enter the expected result in cells B2 and B3 and then select the entire column and use Flash Fill. You can also find the Flash Fill option in the Home tab –> Editing –> Fill –> Flash Fill. Note that in case your original data in column A changes, you will have to repeat the steps again to remove the characters before the delimiter. Unlike the formula method, this method gives a static result that doesn’t auto-update
Using Text to Columns
Another quick way to remove all the characters before a delimiter would be by using the Text to Columns feature. Suppose I have the dataset as shown below and I want to remove everything before the dash. Below are the steps to do this: The above steps would split into separate columns based on the specified delimiter. Note that in this example, I have used a delimiter to split the data into separate columns. You can also use Text to Columns to remove a specific number of characters from the left of a text string as well. For that, you should choose Fixed Length instead of the Delimiter option in Step 1 of 3 in the Text to Columns wizard. Note that in case your original data in column A changes, you will have to repeat the steps again to remove the characters before the delimiter. Unlike the formula method, this method gives a static result that doesn’t auto-updates
Remove All Text On the Left of a Specific String
Sometimes, you may have a dataset where you need to get rid of all the text before a specific text string. For example, below I have a data set where I have employees’ names followed by their telephone numbers. I want to extract only the telephone number from each cell, which means that I want to remove everything which is before the telephone number. As with most data cleaning methods in Excel, I need to look for a pattern that I can use to get rid of everything before the phone number. In this case, it’s the text string ‘Tel:’. So all I need to do is find the location of the string ‘Tel:’ in each cell, and remove everything before it (including it). While you can build an advanced formula in Excel for this, let me show you a really cool way using Find and Replace method. Below are the steps to remove all the text before a specific text string: The above steps would remove everything before the string ‘Tel:’, and you will be left with the phone number only. How does this work? In the above example, I have used a wild card character – asterisk (). Since I wanted to remove everything to the left of the string ‘Tel:’, I added an asterisk before it and used it in the ‘Find what’ field. An asterisk () is a wild card character that can represent any number of characters in Excel. This means that when I ask Excel to find ‘*Tel:’, It is going to look for the string ‘Tel:’ in each cell, and if it finds this string in any cell, no matter its position, everything up to that point would be considered while replacing the text. And since I replaced this with nothing (by leaving the ‘Replace with’ field empty), it simply removes everything up to that string in the cell. This means that I’m only left with the characters after that text string and everything before that text string including that string itself is removed.
Remove All Text from the Left (and keep the numbers)
Sometimes you may get a data set where you have the text and numbers together in one single cell, as shown below, and you want to remove all the text but keep the numbers only. This can easily be done using a simple formula. Below is the formula that would remove all the text from the left of the numbers, so that you’re only left with the number part of the string. The above formula would remove all the text portions from the left part of the cell so that you’re only left with the numbers. Now let me quickly explain how this formula works. The FIND({0,1,2,3,4,5,6,7,8,9},A2) part of the formula would look for these 10 digits in the cell and would return the position of these digits as an array. For example, for sale A2 the result returned by this formula would be {8,#VALUE!,#VALUE!,#VALUE!,6,#VALUE!,#VALUE!,#VALUE!,#VALUE!,7} As you can see, it returns 10 values where each value would represent the position of that number in the cell. Since the digit 0 occurs at the eighth position, the first value returned is 8, and since the cell does not contain the digit 1, it returns a Value error. Similarly, all the digits are analyzed and a number is returned if the digit is present in the cell, and the value error is returned if that digit is not in the cell. Now, this FIND formula is wrapped within the IFERROR formula, so that instead of the value error we get something more meaningful. In this case, the IFERROR formula would return a number when it finds the digit in the cell, and in case it does not find the digit, it would return the maximum length of the characters in the cell (which is done using the LEN formula) This is done to make sure that in case there are cells where there are no numbers, the IFERROR formula would still return a digit that can be used in the formula (else it would have returned an error). Now with these arrays of numbers, I have used the MIN function to find out the minimum value in that array. this would tell me the starting position where the numbers start in the cell. For example in cell A2, it would give me 6 which means that the text part in the cell ends at the 5th character and the numbers begin from the 6th character onwards. Now that I know at what position the numbers start in the cell, I need to know how many characters from the left I need to remove. To do this, I have again used the LEN function to find the total length of characters in a cell, and from this, I have subtracted the result of the main function so that I would know how many text characters are there in the left. Note that I have also subtracted 1 as I want to exclude the first number in the cell (if I don’t subtract 1 in this formula, it would also remove the first number along with the text). And finally, I have used the RIGHT function to extract all the numbers from the right, which essentially means that all the text characters on the left are removed.
Remove All Numbers From the Left
In the previous section, I showed you how to remove all the text characters from the left so that we are only left with the numbers in the cell. But what if the situation is reversed. What if I have a data set as shown below where I want to remove all the numbers from the left and keep all the text characters. We can use a similar formula with some tweaks. Below is the formula that would remove all the numbers on the left so that you only get the text part of the cell. While this formula may look bigger and a little scarier than the previous one, the logic is exactly the same. In this formula, we have used the FIND function to find out the position of all the 26 alphabets in the English language. The FIND portion of the formula checks the cell and gives us the position of each of the 26 alphabets. Note that I have also used LOWER(A2) instead of A2, because the alphabet that I’m using in the FIND formula is in lower case. I have then wrapped the fine formula within IFERROR, so that in case the formula is not able to find the position of a specific alphabet, instead of returning the value error, it would return the length of the content in the cell (which is given by the LEN formula). This is to make sure that in case I have a cell where there there is no text portion there are only numbers, I would still get a numeric value. I then used the MIN function to find out the minimum position where the text starts. This would tell me where the numbers would end and where the text starts so that I can split the content of the cell to remove all the numbers from the left and keep the text portion. Now that I know where the text characters start in the cell, I have used the MID function to extract everything starting from that position till the end. Note that I have used 1000 characters to be extracted within the mid function, but in case your cell has less number of characters, only that much would be extracted. So these are some of the examples where we have removed characters from the left in a cell in Excel. I have shown you formulas to remove a fixed number of characters from the left or remove the characters on the left based on a delimiter. I also showed you how to use a simple find and replace technique to remove all the characters on the left before a specific string. And then finally I showed you two formulas that you can use to remove only the numbers or only the text from the left. I hope you found this Excel tutorial useful. Other Excel tutorials you may also like:
How To Remove Text Before Or After a Specific Character In ExcelHow to Remove the First Character from a String in Excel (Quick & Easy)How to Combine First and Last Name in Excel (4 Easy Ways)How to Extract the First Word from a Text String in ExcelSeparate First and Last Name in Excel (Split Names Using Formulas)How to Capitalize First Letter of a Text String in Excel (using Formula & VBA)How to Extract a Substring in Excel (Using TEXT Formulas)
