Replace text from end of a string starting from variable position

The Excel REPLACE function is used to replace a certain length of text from a given string. We define the starting position, the number of characters we want to remove and the new replacement text. In this article, we will learn how to remove/replace some text from the end of a string starting from a variable position.

Generic excel formula to remove text from variable position

=REPLACE(string,FIND("-",string),LEN(string)-FIND(text,string)+1,"")

string: It is the string from which you want to remove text.

text: It is the text to which you want to remove text.

Let's see an example to make things clear.

Example: Remove time text from data

In this example, we have punch time associated with day names. We want to remove time text from the data and have day names only. The time and day name is separated using a hyphen (-). This will be our anchor text.

Let's use the above generic formula to remove the variable length of characters from the end of the string.

Write this formula in C3 and drag it down.

=REPLACE(B3,FIND("-",B3),LEN(B3)-FIND("-",B3)+1,"")

Hit enter and drag it down. We have stripped out the time and the day is retained.

How does it work?

Let's break down the formula to understand it.

Each formula works inside out. The FIND function returns the location of the given text in a given string. The first FIND function returns the location of the hyphen (-) in every string. For the first string, it returns 8.

Next, the LEN(B3) function returns the length of the string, which is 16.

We again have a FIND function that returns the location of the hyphen in the string which is 8.

Now, the formula is =REPLACE(B3,8,16-8+1,""), which is =REPLACE(B3,8,9,""). It means we need to remove 9 characters from the 8^th position in the string of B3

Now the formula is =REPLACE(B3,8,9,""). As we know that the REPLACE function replaces n number of characters from a starting position with another text. Here, the text is in B3, starting position is 8, the number of characters we figured out is 9 with the help of LEN and FIND function, and the replacing character is nothing (""). Hence the REPLACE function removes 9 characters from the end of the string, which leaves us with day name only.

Notes:

If the given character is not found, the function will return #VALUE error. It is better to use the IFERROR function to catch errors.
The FIND function is a case sensitive function. If you want to do a case-insensitive search then use the SEARCH function.
In the above formula, we have used a hyphen (-) to find the number of characters. You can use any character/s to do so, it can be a colon in excel or any text.

So yeah guys, this is how you can remove a variable length of text from the beginning of the string. I hope it was explanatory. If you have any doubts regarding this topic or any other Excel/2010/2013/2016/2019/365/VBA. We will be happy to help you.

Related Articles:

Remove text from the beginning of a string to variable position | This REPLACE formula helps you removing a variable number of characters from starting of the text.

Remove matching characters in Text | To remove matching characters we won't use REPLACE function. We will use the SUBSTITUTE function. This function automatically removes given text with another text.

Remove leading and trailing spaces from text in Excel | The trailing spaces disturb your data set and it is necessary to remove any trailing or leading space from the text in excel. This formula removes all trailing spaces.

Remove unwanted characters in Excel | To remove unwanted characters we use the SUBSTITUTE function.

Remove Characters From Right | The LEFT function can be used to remove characters from the RIGHT of the text. Actually this function focuses on retaining text on left.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.